Get sql query from powerbi report/dataset

Question

can I get SQL query used to create a dataset in PowerBI using the powerbi report/dataset. I no longer have the desktop file (.pbix).

I only have the report and dataset in PowerBI Service. I had used couple of joins and a couple of where conditions in powerBI desktop. I am trying to recreate the dataset but now I somehow get slightly different version of the dataset.

Can I find the joined tables and where conditions used in the powerBI desktop via the report in powerBI service?

Answer 1

1. Download .pbix file from PowerBI Service and open the report

2. Right click on the Query > Edit query

3. Your Query will be listed in the marked region of the following picture. You can remove #(lf) s and directly use - modify the query in SQL Server Management Studio.

4.) Right Click on the Applied Steps - Source. Then select Edit Settings.

5.) Your original SQL Query will be displayed in the SQL Statement region.

Answer 2

do you have full access to the dataset in the workspace, if so, you can simply locate the dataset, click the three dots and download the file.

Then you can simply navigate to Power Query and locate the M code and retrieve the SQL Statement.

Other that, there's no really easy way to get it, you can also try these methods: https://towardsdatascience.com/how-to-capture-sql-queries-generated-by-power-bi-fc20a94d4b08

Very Alternatively, you can check Power BI REST API, to get the dataset's information and data sources (https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/get-datasources) and then ask the admin of the DB to mine the query that leads to the report directly from SQL Database admin panel.