Introduction
When data is added using the Get Data button 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 ‘Advanced Editor’ Button.
Recently, we had a business requirement from one of our old clients to create a Power BI report. 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 ‘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 it. After a thorough search, we understood that we could customize the report data source and data source queries using the Advanced Editor.
Here, is a look into the steps that we followed to change the 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 the ‘Get Data’ button. Now, we want to execute the same report for the Production environment. To do the same follow the steps given below:
1. 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.
Note: The Power BI report name must be unique
2. Then, click on the ‘Edit Queries’ button to edit the 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 the ‘Advanced Editor’ button, you can see the query for the Account entity 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 ‘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 the ‘Close & Apply’ 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.
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 the ‘Choose Columns’ 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 Advanced Editor.
Now let’s see how it is done
- Once we remove the columns from the data source using the ‘Choose Columns’ button, the new query line will get added to the data source queries. So, we just need to modify that particular remove columns query line (i.e. ‘Removed Other Columns’ query line) from the Advanced Editor
- In my case, I have selected a few columns from the Account entity using the ‘Choose Columns’ button. 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.
Want to enhance business operations with interactive data visualization?
Switch to Power BI for Online, Desktops, and Dashboards with Inogic- Professional Services Division
Connect at crm@inogic.com and subscribe to complete assistance in shouldering the complex operations of revamping the intricate processes in the background related to Power BI