Forum Discussion
Sharepoint Folder access via Dataflow
Hi colleagues!
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.
5 Replies
Hi Anonymous ,
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 .
https://xxxxx.sharepoint.com/sites/<sitename>
You can refer to the link: https://www.poweredsolutions.co/2019/04/04/connecting-to-files-in-sharepoint-onedrive-with-power-bi-power-query/.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-xicai ! Thank you for responding!
The problem occurs when I click combine files:
There is ok in Desktop:
The result:
But in Service:
The result:
I'm not only one who trying to manage such issue. There is the same question: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/37477897-combine-files-binaries-in-dataflows
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"
That is exactly what I do, but it dooesn't work 😞
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.
Copy/past the M-code from the PBI Desktop to PBI service to create a Dataflow normally works, but not with Sharepoint folders, it's better to do the first steps to connec to the Sharepoint folder from your dataflow interface and follow the steps to transform it (then you could copy paste other steps after your table is visible).
In my case, I was importing an excel file that contained many tabs, at certain point one of the steps (after imported excel workbook I believe) I had to click on the tab that want to expand and somehow this was not recorded as step on M-code.
