cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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.

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Sharepoint Folder access via Dataflow

Hi @gerasimovav ,

 

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.

85.png

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-....

 

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.

 

Highlighted
Helper III
Helper III

Re: Sharepoint Folder access via Dataflow

Hi @v-xicai ! Thank you for responding!

The problem occurs when I click combine files:

 

There is ok in Desktop: 

image.png

 

  

 

 

 

 

 

The result:

image.png

 

 

 

 

 

 

But in Service:

image.png

 

 

 

 

 

 

 

The result:

image.png

Highlighted
Helper III
Helper III

Re: Sharepoint Folder access via Dataflow

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-i...

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 😞

Highlighted
Frequent Visitor

Re: Sharepoint Folder access via 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 :

  1. Delete the “Filtered hidden files” step in the main query AND in the helper queries (the ones in the little folder) too.
  2. 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.

 

WillTickel_0-1593698971497.png

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors