I'm trying to move PowerBI Desktop DataSources to Power BI Service Dataflows. The best way I found is just coping scripts of Anvanced Editor in Desktop's Query Analyzer to Service's Query Analyzer. I've got stuck in Sharepoint Folders. If I just copy power query script (which successfully works in Desktop) to dataflow I get an error "Expression.Error: The import Transform File matches no exports. Did you miss a module reference?". If I try to create a new Dataflow with Sharepoint Folder, I get an error "Invalid credentials. (Session ID: xxxx)" at the start (but credentials are completely the same). I found out that Power Desktop uses "Source = SharePoint.Files("https://url", [ApiVersion = 15])". If I use the same string in DataFlow, it works. But when I want to expand file's content I get "We encountered an unexpected error."
So I couldn't get access to files it Sharepoint Folder.
By my test connecting to SharePoint folder in the latest version 2.76.5678.782 of Power BI Desktop, then copy the scripts in Advanced Editor to a new blank query of dataflow in Power BI Service, click refresh button, it works well.
If you want to connect to SharePoint online folder in Power BI Desktop, please use SharePoint folder connector and use the URL as below, and sign in with organizational account .
There is workaround suggested: "copy & paste the main query code from the Desktop advanced editor to the dataflow, copy the query code from the custom function for combining files from the Desktop advanced editor and somehow work it into the main M code in the dataflow"
This is only relevant if you have a power bi dataflow that’s pulling from a sharepoint folder (if not you can stop reading now).
In the last 24/48hours Power BI have changed something which means all power bi dataflows that are pulling from folders will break. For some reason one of the automatic steps “Filtered hidden files” is now causing an expression error. It’s a relatively simple fix :
Delete the “Filtered hidden files” step in the main query AND in the helper queries (the ones in the little folder) too.
Go to the “Removed other columns” part of the query that preceeds expanding the data. Expand the data again, the new step will be called “Table.ExpandTableColumn" and you can then delete the old expand data step “Table.ExpandTableColumn”.
It should all work and refresh properly after that if your problem was anything like mine.