SQL Query behind report !

[Anonymous]

Hi

I have a report which is based on 4 tables/views which are linked in a model relationship.

The report was written by someone else who has left.

I am a SQL guy and I cant work out how to see what the report is basing its query on. I know I can select one of the views and look at the native query using power query but I cant see how I can view the whole query with the linked tables or even the query behind the model

Can someone help me

[Anonymous]

Thanks but I dont think I am explaining myself correctly. Yes if I go into power query I can see the 4 tables and views but I cant see the full query that represents the model when all the tables/views are joined together. If I try to simulate the sql query for the model using 3 joins the data does not match what powerbi shows so I just want to see what the combined sql query is that gives me the result in a visual bases of course whatever filters have been applied..

does that make sense.. thanks

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

There is no single query that represents the model.  The data you see presented in the visuals is a mix of the tables in Power Query and the Columns and Measures created in Power Pivot (Data View).

You are asking to see a combined SQL query - even if all the data is loaded from a SQL database there are still transformations made in Power Query and other modelling happening in Power Pivot/DAX that are not part of any SQL command.

Regards

Phil

[PhilipTreacy]

Hi @Anonymous 

I'm not sure if you are expecting or know that there is a SQL query/queries pulling in data?

If you've looked in Power Query then you can see where the data tables are being loaded.

From there the data is loaded into the Data Model(Power Pivot) which is what you see when you click on the Data view.  Lots of tables.

In the Data View you can also add Calculated Columns of data, and Measures.  Measures are used to calculate data  for reporting but aren't stored in columns and are viewable in the Report page visuals.

In the Report page you'll have various visuals.  If you click on one of these you'll seeon the right hand side the data that is loaded into it.

For example

The data displayed in these visuals is from the list of tables/columns/measures under the Fields area.  This data is what was created in Power Query and Power Pivot (Data Model) and relies on the relationships constructed between tables, which you can see in the Model view.

Anyway, that's a crash course in Power BI, hope it's helpful.

Regards

Phil