Introduction
In Power BI, when we add data using Get Data button by connecting to the services, Power BI generates a query in background and we can modify this query and its data sources using ‘Advanced Editor’ Button.
Recently, we had a business requirement to create Power BI report. So we created the Power BI report for Sandbox environment. And after creating the report we wanted to execute the same report for Production environment. Usually, we change the report data source from ‘Data Source Setting’ as there is a ‘Change Source’ Button available to change the report data source. But in our case the ‘Change Source’ button was not enabled and we were not able to change the data source of the Power BI report using ‘Change Source’ button. After a thorough search, it was found that we can customize the report data source and data source queries using Advanced Editor.
Now let’s have a look into the steps to change Data Source using Advanced Editor.
In the following case, we have added Account and Contact entity data for the Sandbox environment from Common Data Service using ‘Get Data’ button. Now, we want to execute same report for the Production environment. To do the same follow the steps given below:
1. Make a copy of Power BI report created for Sandbox environment and rename it. After renaming, open new report (i.e. renamed report) in Power BI Desktop.
Note: Power BI report name must be unique.
2. Then, click on ‘Edit Queries’ button to edit data source query as highlighted below.
3. After that you will be redirected to the new window, which will show you the data sources of your Power BI report.
4. Then, select query from data source queries and click on ‘Advanced Editor’ Button as shown below. Here, I am editing the data source of Account entity query.
5. After you click on ‘Advanced Editor’ button, you can see the query for Account entity in Advanced Editor Window. And to change the Data Source of your report, here you just need to change the API URL (i.e. you need to add the URL of Dynamics CRM environment to which you want to move this report) as highlighted below. And click on ‘Done’ button.
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.
6. Next, click on ‘Close & Apply’ Button to apply changes on Power BI report. And you will see the data of newly added Dynamics CRM environment in Power BI report.
In the same way we can modify other query parts as well.
For example: Suppose, you have removed unnecessary columns from data source queries using ‘Choose Columns’ button and you want to add any of those removed columns in data source or want to remove selected column. We can do this with the help of Advanced Editor. Now let’s see how it is done.
- Once we remove the columns from data source using ‘Choose Columns’ button, the new query line will get added in the data source queries. So, we just need modify that particular remove columns query line (i.e. ‘Removed Other Columns’ query line) from Advanced Editor
- In my case, I have selected few columns from Account entity using ‘Choose Columns’ button. And in Advanced Editor, the new query line is added to choose selected columns and remove other columns as shown below:
- Here, I want to add ‘Account Number’ column of Account entity in Power BI report data source. So, we can add the column in ‘Removed Other Columns’ query line as shown below:
- Once you click on ‘Done’ button you will be able to see that column in Fields section of Power BI report as highlighted below:
Conclusion
In this way, by using ‘Advanced Editor’ button we can easily change the Data Source of the Power BI report as well as edit the Data source queries.
