How many times has someone built a Power BI report and finally had the source of the data changed to a new location? Well, this blog walks everyone through a set of steps to assist transitioning of the query without breaking the Report’s charts.
Situation: Source location of a file which is referenced to build a Power BI report has been moved to a different location or the updated file needs to be now referenced from another location while also ensuring that the reports that were built remain intact.
Problem: When trying to refresh the data in Power BI, the file is unavailable in the previously pointed location or the file has not been updated and this causes a failed refresh of the queries in the Power BI report.
What this blog answers: How to successfully point the Query in the Power BI Report to the new file location and perform a complete refresh.
A look at the Power BI report built from a .csv file available in my local “C:” drive:
Looking at the data source and Query behind it:
The Power BI report shown above reads the data from the local drive and the respective queries can be seen below:
Moving the data source to a different location:
Original Data Source Location: Local C Drive
New Data Source Location: SharePoint
SharePoint Site: Microsoft Analytics POC
Changing the names of the .csv files in our original location:
Refreshing the Power BI data:
Changing the Power BI query to point towards the new data source:
Note: Make sure that the data file that is moved to the new location is still in the same format as the previous file that Power BI had been referencing on. This helps us tag the Fields that were used in the visualizations, in a way where the charts remain intact.
Entering the SharePoint Site URL in our Connector dialog box and logging in with the SharePoint credentials:
Instead of updating to a new query, click on each of the tables and update the existing query (which currently searches for the .csv file in the local C drive), to point to the SharePoint location.
Click on Advanced Editor of the new query which we created with the SharePoint connectivity and copy the query available there.
Paste the SharePoint query and replace it onto the “Car_Info Master Data” query:
Click on Done and then choose the actual .csv file from which the fields must be read from.
Note: At this point do not forget to mention that the first row of your file has the headers for the data. Sometimes the column headers might be available in the first row of the data set and that might lead Power BI to refer each column as “Column 1” through “Column n” which are not identified as the required fields by the visualizations that were previously built.
Similarly, do the same for the remaining Queries in your Power BI report and point them to the right data source location and the file in there.
Try opening the Advanced editor for one of the queries, to check how the data source settings have been updated:
Delete the latest queries that were actually created for the new SharePoint connectivity as we do not plan to read new data sources.
Exit the Power Query Editor using “Close and Apply” which also initiates a refresh of the data from the new Source (SharePoint).
This brings us to a conclusion where we clearly showcase that data source is just used for referencing the data from a location for building reports. If the data source had a change in the location, then it is not necessary to re-build the visualization charts again, but instead, the Query that Power BI has built for reading the data can be changed by just using these simple above mentioned steps.
For any further questions, feel free to reach us at Visual BI and stay tuned for more blogs.