{"id":21159,"date":"2019-11-22T09:03:06","date_gmt":"2019-11-22T09:03:06","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=21159"},"modified":"2024-01-08T12:05:38","modified_gmt":"2024-01-08T06:35:38","slug":"how-to-modify-power-bi-report-data-source-queries-from-advance-editor","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2019\/11\/how-to-modify-power-bi-report-data-source-queries-from-advance-editor\/","title":{"rendered":"How to modify Data Source Queries of Power BI Report using Advanced Editor"},"content":{"rendered":"

Introduction<\/strong><\/h2>\n

When data is added using\u00a0the Get Data<\/strong>\u00a0button by connecting to the services, Power BI generates a query in the background and we can modify this query and its data sources using the \u2018Advanced Editor<\/strong>\u2019 Button.<\/em><\/p>\n

Recently, we had a business requirement from one of our old clients to create\u00a0a Power BI report<\/a>. Thus, we created a Power BI report for the Sandbox environment. After creating the report, we had to execute the same report for the Production environment. Usually, we change the report data source from \u2018Data Source Setting<\/strong>\u2019 as there is a \u2018Change Source<\/strong>\u2019 Button available to change the report data source. But in our case, the \u2018Change Source<\/strong>\u2019 button was not enabled and we were not able to change the data source of the Power BI report using it. After a thorough search, we understood that we could customize the report data source and data source queries using the\u00a0Advanced Editor<\/strong>.<\/p>\n

Here, is a look into the steps that we followed to change the Data Source using Advanced Editor.<\/em><\/p>\n

In the following case, we have added\u00a0Account\u00a0<\/strong>and\u00a0Contact<\/strong>\u00a0entity data for the Sandbox environment from Common Data Service using the \u2018Get Data\u2019<\/strong>\u00a0button. Now, we want to execute the same report for the Production environment. To do the same follow the steps given below:<\/p>\n

1<\/strong>. Make a copy of the Power BI report created for the Sandbox environment and rename it. After renaming, open a new report (i.e. renamed report) in Power BI Desktop.<\/p>\n

Note: The Power BI report name must be unique<\/em><\/strong><\/p>\n

2<\/strong>. Then, click on the \u2018Edit Queries<\/strong>\u2019 button to edit the data source query as highlighted below<\/p>\n

\"modify<\/p>\n

3<\/strong>. After that you will be redirected to the new window, which will show you the data sources of your Power BI report.<\/p>\n

\"modify<\/p>\n

4<\/strong>. Then, select query from data source queries and click on \u2018Advanced Editor<\/strong>\u2019 Button as shown below. Here, I am editing the data source of Account<\/strong> entity query.<\/p>\n

\"modify<\/p>\n

5<\/strong>. After you click on the \u2018Advanced Editor\u2019 button, you can see the query for\u00a0the Account<\/strong>\u00a0entity in the Advanced Editor Window. To change the Data Source of your report, you just need to change the API URL (i.e. you need to add the URL of the Dynamics CRM environment to which you want to move this report) as highlighted below. Click on the \u2018Done<\/strong>\u2019 button.<\/p>\n

\"modify<\/p>\n

Note: This will work only if columns (i.e. attributes) selected in Power BI data source are present in other environment in which you are going to run this report.<\/em><\/strong><\/p><\/blockquote>\n

6<\/strong>. Next, click on the \u2018Close & Apply<\/strong>\u2019 Button to apply changes to the Power BI report. You will see the data of the newly added Dynamics CRM environment in the Power BI report.<\/p>\n

In the same way, we can modify other query parts as well.<\/em><\/p>\n

For example:<\/strong>\u00a0Suppose, you have removed unnecessary columns from data source queries using the \u2018Choose Columns<\/strong>\u2019 button and you want to add any of those removed columns in the data source or want to remove the selected column. We can do this with the help of\u00a0Advanced Editor<\/strong>.<\/p>\n

Now let\u2019s see how it is done<\/em><\/p>\n