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

[TomRobbins]

I'll try to keep this simple.  

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.

[AnkitKukreja]

Hi @TomRobbins 

I would try clearing caches in Desktop and also delete the existing dataset from service and try republishing it as a new dataset. In case if this still shows you the same error, then my guess is you still might be missing a folder connection or table into this model.

[TomRobbins]

Yea I've tried all the obvious suspects.  If you created a local folder connection in the wizard and it shows in your data sources, that does not get changed if you manipulate the code in the advanced editor.  Even though I've changed the source to Sharepoint folders in the editor, the "data source settings" never reflect that.  And the data source settings is what the pro service is looking at when you publish it.

It's a simple thing to recreate to see what I mean.  Create a data source to a local folder.  Change the advanced editor to point to a SharePoint folder.  Notice the data source settings do not change.

So, how do you change the data source settings to match the changes made in the M code??