Queries and Tables in Power Pivot and Data Model

[DashboardNovice]

In the attached file, if you look at the available fields in the pivot table and mouse over them, the ones with the table and disk icon say "Data Source: Query" and the ones with just a table icon say "Data Source: Table."

1. In some cases like the attached picture, the queries don't have a corresponding table icon. What causes this?

2. In some cases like the attached picture, there is a table icon (Price_Threshold) without a corresponding query icon. What causes this?

3. In the attached file, why are some fields bold and others are not?

4. In other cases I created a pivot table then I noticed that every query also had a table by the same name. When I added a query to the pivot table, I got different results compared to when I added the table (of the same name as the query). What causes a corresponding table icon to be displayed with a query icon by the same name (the attached pic doesn't show this situation)?

 

[Chihiro]

You have source table in same workbook. That would cause same table to appear. But not as data model. Hence, missing query icon.

PQ will by default name the query with same name as source table in the same workbook. External sources are named as Queryx by default.

If you query from same workbook, but use named range or worksheet as source, you will not see the source as there is no table object.

 

[DashboardNovice]

You have source table in same workbook. That would cause same table to appear. But not as data model. Hence, missing query icon.

PQ will by default name the query with same name as source table in the same workbook. External sources are named as Queryx by default.

If you query from same workbook, but use named range or worksheet as source, you will not see the source as there is no table object.

If you can answer these questions, I think I will understand what you are saying.

1. Let's say you are in workbook A, then you open the Data Model. Is it possible to import a table from the Data Model of a different workbook, let's say workbook B?

2. Another scenario, let's say you are in workbook A. While you are in workbook A, is it possible to refer to a table in the Data Model of a different workbook, let's say workbook B?

 

[Chihiro]

1. No, not directly. Though you can always set up query/connection for your environment and use that to import data into the model.
2. No. That is not possible. You must have query/connection defined in the workbook that houses model.

If you have access to PowerBI service (Pro/Premium SKU) and PowerBI Publisher (Excel Add-in from Microsoft)...
You can build your model in PowerBI Desktop, publish to PowerBI service. And then consume it through PowerBI Publisher (or use other methods).
However, all have specific license SKU requirements. Either on Excel side or PowerBI side.

Create Excel workbooks with refreshable Power BI data - Power BI

Learn how to build refreshable reports in Excel with Power BI semantic models so you can analyze the data like you would with a semantic model that is local to Excel.

docs.microsoft.com