Blogs / Microsoft / Power BI / Implementing Source Data Location Change in Existing Power BI Reports

Implementing Source Data Location Change in Existing Power BI Reports

Nov 12, 2018

SHARE

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.

Problem Setup:

A look at the Power BI report built from a .csv file available in my local “C:” drive:

Implementing Source Data Location Change in Existing Power BI Reports
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:

Implementing Source Data Location Change in Existing Power BI Reports
Implementing Source Data Location Change in Existing Power BI Reports
Implementing Source Data Location Change in Existing Power BI Reports
Moving the data source to a different location:

Original Data Source Location: Local C Drive

Implementing Source Data Location Change in Existing Power BI Reports

New Data Source Location: SharePoint

SharePoint Site: Microsoft Analytics POC

Implementing Source Data Location Change in Existing Power BI Reports

Changing the names of the .csv files in our original location:

Implementing Source Data Location Change in Existing Power BI Reports

Refreshing the Power BI data:

Implementing Source Data Location Change in Existing Power BI Reports

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.

Implementing Source Data Location Change in Existing Power BI Reports

Entering the SharePoint Site URL in our Connector dialog box and logging in with the SharePoint credentials:

Implementing Source Data Location Change in Existing Power BI Reports
Implementing Source Data Location Change in Existing Power BI Reports

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.

Implementing Source Data Location Change in Existing Power BI Reports

Paste the SharePoint query and replace it onto the “Car_Info Master Data” query:

Implementing Source Data Location Change in Existing Power BI Reports

Click on Done and then choose the actual .csv file from which the fields must be read from.

Implementing Source Data Location Change in Existing Power BI Reports
Implementing Source Data Location Change in Existing Power BI Reports

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.

Implementing Source Data Location Change in Existing Power BI Reports
Implementing Source Data Location Change in Existing Power BI Reports
Implementing Source Data Location Change in Existing Power BI Reports
Implementing Source Data Location Change in Existing Power BI Reports

Try opening the Advanced editor for one of the queries, to check how the data source settings have been updated:

Implementing Source Data Location Change in Existing Power BI Reports

Delete the latest queries that were actually created for the new SharePoint connectivity as we do not plan to read new data sources.

Implementing Source Data Location Change in Existing Power BI Reports

Exit the Power Query Editor using “Close and Apply” which also initiates a refresh of the data from the new Source (SharePoint).

Implementing Source Data Location Change in Existing Power BI Reports

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.

For any further questions, feel free to reach us at Visual BI and stay tuned for more blogs.


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This Blog!

Share this blog with your friends and colleagues!