Unable to change path for datasource in uploaded pbix file in powerbi.com

[mliljenberg]

I have a pbix file with connections to two different Excelfiles. In powerBi desktop, it works and I have change the source pointing to the correct location on the desktop. The files were previosly stored in a different location(Different user, Onedrive for business location). I have updated the pbix file to reflect the new source location and saved it. Opening up the pbix file on the desktop app shows the right location.

Now to the problem, when uploading to powerbi.com it ads connections under manage datasources to the old location as well as the new location for the excelfiles, I cannot modify this path, cannot delete it. This happens with more than one pbix file.

I have tried reinstalling the desktop gatway, moved all the files to a different location before uploading, I have saved the pbix file in a different location and uploaded it,. I have used Edge as well as chrome. I have tried publishing and uploading them both.

I cannot figure out how to get rid of legacy datasources in powerbi.com? Does anyone know? How can I clear the datapath for local datapath that are no longer there?

[Anonymous]

Hi folks,

Today I ran into the very same problem. I have a data source to a file in SharePoint Online. The file was moved to a different location in SharePoint. I updated the path in Power BI Desktop accordingly, then published the PBIX file to the Power BI Service.

Now the data refresh in the Power BI Service doesn't work anymore, because the data source is duplicated: both the previous version and the new version show up. And the old one obviously doesn't work anymore.

This makes the refresh impossible. There is an extra - and broken - data source in the web, which doesn't exist on the desktop app. I deleted the data set, then re-published it: that didn't change anything. There is a ghost datasource, which is visible only in the web service.

I saw in this thread that this was supposed to be fixed a year ago. Well, it isn't. Or it came back. Anyways, it needs fixing. I can reproduce the issue.

[Arnaud91]

When I upload and configure this report https://gallery.technet.microsoft.com/PowerON-Power-BI-ConfigMgr-0cd61c5f to our on premise power bi (January 2019) I have a very strange issue, it shows me 2 datasource on the Data sources tab in the report server web page but i see only one in power bi desktop...

The first one is

pdms01ps1;CM_P01

 Probably a test when the power PI report was generated and as it is non existent in my environement i cannot schedule a refresh of the report...

Any idea ?

Arnaud

[ALinberg]

In case you are still having trouble with this, it looks like this issue may have been fixed with the April 2016 release.  You can edit the queries - and for those with a file path that is no longer valid (or has moved) you can update the file path.  

I also found this helpful video that shows how to set up a query parameter to account for different file path locations - published by Curbal

https://www.youtube.com/watch?annotation_id=annotation_2341374257&feature=iv&src_vid=JER0QXmL34k&v=6...

[Roland]

Same issue here with refreshing data from an Excel model stored on OneDrive for Business.  This is not an experience which allows me to demo to potential clients the ability to update data and see the dashboards refresh via manual or automated data refreshes.  Hardly a compelling experience.  Surely there must be a way to handle this core functionality?

[jimmyhaley]

This remains a problem without a true solution - anytime you do anything to a datasource (change the location, rename, etc), the old/legacy datasource persists upon upload to the PBI site.  Uninstalling everything and rebuilding connections is not a solution, but a huge inconvenience bordering on dealbreaker for using an expected-scalable solution.

Ideally, yes - all datasources, when added, would stay exactly where they are, but that's not always the case.  In my situation, I've updated a datasource and changed the location from drive to drive on our server - the result was fine within the PBI Desktop environment, but once pushed to the PBI site, the old datasources remain - since there's no way for me to enter credentials for a file that doesn't exist anymore, it won't allow me to refresh on the PBI site any longer.

For instance, let's say I have an excel datasource located here: C:\datasource.xls

If I move that same datasource to E:\datasource.xls, update everything in the PBI Desktop file (so it works flawlessly), then push it to the PBI site, I now have two datasources called datasource.xls - one located on my C drive, one on the E drive.  I've reviewed everything - the datasources under options, the individual queries...there is no mention of this in the file itself.

It appears that the data persists on the upload in some sort of "cached" state on MSFT's servers - the result is that you have a dashboard looking for data that isn't there.  Instead of overwriting the file, it just appends it almost.

[Luuk]

Same problem here when changing the data source from a development server to a production server.

[Greg_Deckler]

If you delete the data set (ellipses, delete) then it will no longer be listed as a data source in the service. Perhaps you need to delete the data sources in your PBIX file? File | Options and Settings | Data sources, pick the data source and "Delete". So, remove the data sets in Power BI Service, remove data sources from PBIX file and then upload your PBIX file.

[mliljenberg]

Thanks for the suggestion, unfortunately it made no difference. I had deleted and uploaded the dataset many times before but I hadn't deleted the data source from powerbi desktop before uploading it. I did that now as you suggested but it made no difference. 

I wonder where these are stored as they are not anywhere in the pbix file that can be edited at least...

[Greg_Deckler]

Hmmm, I'm going to have to spin up an experiment on that one, that's really weird if they are gone from data sets, gone from PBIX file and you upload the new PBIX file and they reappear. Very odd.

[mliljenberg]

After much frustration, I managed to get it to work. What I needed to was to completely uninstall Desktop gateway as done before, but in conjunction with completely uninstall Power BI desktop and then open the pbix file fresh again, now it rebuilt all connections and after saving and uploading it to powerbi.com no it didn't try to connect to "ghost" data sources wheren't there anymore.

Refresh unfortunately fails though both manually and scheduled although they work on the desktop, but that is another story and one that I will raise in a  different thread. Thank you so much for helping out Smoupre!

[Greg_Deckler]

Happy to help, I've had success with local Excel files but not Excel files on network drives...