Existing PowerBI installation - how to find/modify the SQL query for a table?

[IanMurphy]

I've inheirited a PowerBI installation which I need to both clean up and make some modifications to. 

What I've got is a collection of dashboards in O365 which are linked to an onprem ERP. There is a PC with PowerBI components installed on it which I believe is the PowerBI 'server'/gateway for O365.

In o365 I can see the dashboard and its dataset. If I examine the dataset, it has several tables. I am able to open the data model and see the relationships between the tables. However I need to add some extra information to these tables and I am unable to see where the link is to actual underlying SQL queries.

I would guess that on the onprem PC with powerbi I should be able to open the dashboard from the desktop app and to view the queries. What I can't work out is the steps to do this. 

I understand that there a 'power bi gateway' package, but I can't find this installed on any systems, just the PowerBI desktop.

On the sql server I am able to modify the view used and to add a column, but I presumably need to run some sort of a 'design refresh' to get PowerBI to include that column in its import process. What is that process even called and how to I work out where it is run?

Anyone able to point me in the right direction? I'm probably just missing a crucial step here.

[IanMurphy]

ok, thanks. I think I'm getting the hang of where to find the varios components. Lets see if I can make a modification without breaking anything.

[v-yueyunzh-msft]

Hi , @IanMurphy 

According to your description, you noe have a dataset in Power BI Service , but you need to modify a M query for a table in it .

If this is the case, you need to download the pbix file of the dataset, then open it locally, and download it in Power Query Editor.

For more information, you can refer to:
Download a report from the Power BI service to Power BI Desktop - Power BI | Microsoft Learn

And then you can modify it in your on-premise conmputer and then you need to republish the dataset and configure the refresh of this dataset again.

And you also can see the datasouce by "View Lineage" of the dataset:

And we can see the gateway configuration in the setting of the dataset if you have the permission.

By another , the data gateway just a service or application to keep the connection for your on-premise and Power BI Service so that the power BI Service can get the data from your data source.

For this , you can refer to :
On-premises data gateway - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly