Problem publishing after changing data source from local folder to SharePoint folder

[TomRobbins]

Built tons of things in PBI Desktop all using local Folder combinations of files as data source. We assumed since these were OneDrive synced folders that we could easily publish to the Pro service when ready. We can not because the data source in the .pbix is a local path regardless that it's synced via OneDrive (but it's called Onedrive)...... Big mistake

Anytime we publish it says it needs a gateway for the refresh. Got it. That makes sense.

So, I went into Advanced Editor and changed the M code to change the source to a SharePoint Folder. Got all the queries working correctly and reports all work. I even removed the local folders to make sure it's using the synced SP folders.

But after publishing the new file, the Pro service still thinks all the data sources are local paths and need a gateway.

So, somewhere in the .pbix it's telling the Pro service the data sources were local which is what I originally set them up as via the GUI. Even though I changed the M code to point to SP folders and everything works great.

So, does anyone have any clue here? The only option is to completely create new queries using the SharePoint folder data source option which breaks everything from relationships to visuals.

[lbendlin]

In the Data source settings check that you don't have any leftover local sources.

[TomRobbins]

They are all leftover data sources pointing to local paths.  But like I mentioned, I updated the code to point them all to sharepoint folders.  Problem is, the original data source settings don't change to reflect the redirection in the M code.  So that's where I'm stuck.  I can't find any way to change those original data source settings.  It all works great using new SharePoint folder paths but the settings still say folder paths.  And I don't want to have to delete the queries and build new ones just to please the pro service when I publish.

[TomRobbins]

Data source settings always retain what was initially set up regardless of whether you change the M code to use some other data source.  M code uses sharepoint.files but Data Source Settings dialog window still says folder path.  But it's indeed using the sharepoint location.  Changine the M code does not appear to reflect in the data source settings.

[lbendlin]

That is incorrect, at least for "Data sources in this file".  Go through all of your queries - one of them must still reference a local source.

[v-yueyunzh-msft]

Hi , @TomRobbins 

According to your description, you update the M code to change the on-premise datasource to the sharepoint folder.

But it still seem to a local data souce.Is your SharePoint Folder a shared folder on your local computer?

Whether the connector you are using is still "Folder.Files" instead of the "SharePoint.Files".

If you still use the "Folder.Files" in the M code , this may still be considered as an on-premises data source.

You can also see the data source crecdential you are using in Power Bi Desktop . Then you can publish to Power BI Service to test.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[TomRobbins]

Thanks for the reply.  It's sharepoint.files in the M code, but that doesn't change what you see under data source settings which still says c:\users\xxxx\xxxxx folder path.  And it indeed uses the sharepoint files as I removed the local files to be sure.  Seems to me like the service looks at the data source settings when you publish to determine the data source settings.  Doesn't appear to be anywhere to override that.

[v-yueyunzh-msft]

Hi , @TomRobbins 

All data sources in your dataset should still include local data sources. Yes, currently on Power BI Service, all data sources need to be configured with the correct credentials for scheduled refresh.

If you don't want to use a gateway, you can completely delete all on-premises data sources and republish them so you don't need to use a gateway.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[TomRobbins]

Yea, that's the problem.  All of the reports were built pointing local drive that is synced via OneDrive.  That was our mistake.  We thought it would be smart enough to know it was a OneDrive location. 

Opened a ticket with Microsoft because there should be a way to have the original data source settings match what is in the M code.   Right now the original data source settings points to local folder path but the M code uses a SharePoint path and that works great.  But in publish the service ignores what the code does and looks at any original settings you configured.