Dataflows: Import from Sharepoint folder

[vehau1]

I want to gather all my dataflows in one app space. Part of this data is an Excel Workbook that's placed in a Sharepoint page that I own.

I want to connect to and import this Excel file as a dataflow. However, I cannot choose "Sharepoint Folder" as my Data Source choice. Do you have any clues as to what I can do to get this file as part of my dataflow?

Thanks.

[v-yuta-msft]

Hi vehau1,

There may be occasions when connecting to data sources for dataflows run into issues. This section provides troubleshooting tips when such issues arise.

• Salesforce connector - Using a trial account for Salesforce with dataflows results in a connection failure with no information provided. To resolve this, use a production Salesforce account or a developer account for testing.

• SharePoint connector - Make sure you supply the root address of the SharePoint site, without any subfolders or documents. For example, use link similar to the following: https://microsoft.sharepoint.com/teams/ObjectModel.

Reference: https://docs.microsoft.com/en-us/power-bi/service-dataflows-create-use.

Regards,

Jimmy Tao

[vehau1]

Thank you for the response.

Regarding your Sharepoint address, I am aware of this. I am able to use the connector "Sharepoint Online List" to import the list of objects that holds information in place on my Sharepoint Site, providing the URL to the site as you described.

However, I can't get the actual files stored on the Sharepoint site unless I use the "Sharepoint Folder" connector

This connector, "Sharepoint Folder", isn't even available in the "Get Data"-area when I try to create a new entity. So I'm stuck.

[graemeg]

Appreciate this post is a few years now.  I was looking to do the same as above.  I solved this, and quickly, setting up the connection in the Power BI desktop and making sure it worked.  I then went into Power Query for the connection and copied the Power Query code from the Advanced Editor into a blank Dataflow.  It worked.

[Veles]

@vehau1

I've managed to find a workaround. Select Blank Query and then enter the below as the source. Click next and you can go from there. You would just enter the URL that you would normally do when you select SharePoint Folder in the desktop version.

Source = SharePoint.Files("https://[YOURSITE].sharepoint.com/sites/[YOUR SITE]/", [ApiVersion = 15])

It looks like it's a bit cut down though so there's no button to automatically combine files together as you can in desktop.

EDIT: Managed to get the file combination working with a bit of tweaking - I found it easier to create the query and desktop then copy the whole lot over into a blank query.

[smagbuo]

I'm doing the same workaround but haven't been successful. I'm copying every query associated to the combine method including the fx (invoke function) since the main query (Query1 - which combines all the files in the specified SharePoint folder) is breaking due to missing referenced module. Could you share here the steps you did? Did you have to copy over all the Sample Queries within Transfor File from Query folder as well?

[Veles]

Yeah I copied over all the queries that were automatically created by the combine files step. I seem to remember having similar issues but managed to get it working eventually somehow. Unfortunately I can't remember exactly what I did as this was a while ago 😞

What it might be worth trying is not copying over the function. instead copy over the sample query that it's being created from. Once created in dataflows you can right click on it can select create function.

Also make sure that all the names of the queries are exactly as they are in your original query you built in desktop, even down to the case as Power Query is case sensitive.

[smagbuo]

I'm stuck with the "create function". This isn't coming up as an option when right-clicking on the query. @Veles could you please provide a screenshot? 

[Veles]

Had a look and I can't see it on the menu either.

It looks like I have copied over the "Sample File" parameter and then the function itself from what was created automatically in Power BI Desktop.

[Shelley]

@Veles and @smagbuo I'm not sure if you're having the same problem I was having, but I suggest visiting this thread:

https://community.powerbi.com/t5/Power-Query/Combining-files-from-SharePoint-Folder-using-Dataflows/...

@Anonymous helped me figure out how to recreate my queries from the desktop into data flows.

[Shelley]

Yes, I agree with @smagbuo, will you please share how you did this in order to add the Transform Query steps into the dataflow?

[Anonymous]

Please consider editing to read " I found it easier to create the query in Power BI Desktop, then copy the code from advanced editor over into a blank query."

[Anonymous]

Does anyone know if using this method of accessing sharepoint files and pulling them into a dataflow is subject to a size limit?  It seems that when I use this metholdology to access smaller files, manipulate them in a query and then save them as a dataflow it works fine.  But I have a few situations then the transformations require appending and merging data from separate tables.  It seems to save OK, but when I go to refresh the dataflow I get an error similar to this  - makes me think that larger more complex transformations using this metholdology fail for some reason - any thoughts?

Run time	Dataflow name	Dataflow refresh status	Entity name	Start time	End time	Entity refresh status	Error
24/04/2019 11:20:55	Report	Failed	Report	24/04/2019 11:20:55	24/04/2019 11:21:26	Failed	Error: We're sorry  an error occurred during evaluation. Request ID: 0500d1d7-d106-7e47-0987-fa523e7924a7 Activity ID: 4486d774-3b83-4623-9f18-12d17b06e662

[Veles]

Yep, I have the same issue.

I need to combine multiple files in a SharePoint folder which I can do fine in Power BI but there's no SharePoint Folder connector available in dataflows 😞

[uhdacarter]

And, 3.25 years later, Combine files from SharePoint Folder is still not an available Connector for Dataflows. (At least, not in Power BI Pro, that I can tell.)