Power BI. How to connect up to a sharepoint folder containing multiple files using a dataflow

We have a current use case where we have a Sharepoint folder that will contain monthly files.

We want to create a dataflow that contains everything that we need to pull into a shared data set

The point of this is to have one data set filtered to the correct folder you wish to use.

Then bring in all the data into one data flow. This means that each file should have an identical spec or you will get errors

Step 1 is to open up a Power BI Service and create a dataflow

Ensure you know where your files are in Sharepoint

There are two example files and we have the base Sharepoint URL

In Power BI Desktop get Data

We want Sharepoint online folder

Add in the base address of your Sharepoint site as above and click Next

At this point you can see all your files in all the folders in Sharepoint

Just click OK. we don’t want to combine all the files from all the folders.

What you want to see now is just one applies step (source)

and we can see the two files that we want to merge. Scroll to folders and then filter on the folder

Click on OK

We are left with the two files in the selected folder

click on the arrows against Content

Its using Example File as the first file. In this example click on IDB to continue and OK

The Data is now in Power Query Editor. We have a Source name column which contains the files. Here we can see our two files.

Notice that you have additional Queries. along with your query

  • There is a Sample file
  • A Parameter
  • and a function

Sample File

let  
Source = SharePoint.Files("https://Co.sharepoint.com/sites/TeamDataStore/", 
[ApiVersion = 15]),  
#"Filtered rows" = Table.SelectRows(Source, each [Folder Path] = "https://Co.sharepoint.com/sites/teamDataStore/Shared Documents/General/Submissions/"),  
#"Filtered hidden files" = Table.SelectRows(#"Filtered rows", 
each [Attributes]?[Hidden]? <> true),  
Navigation = #"Filtered hidden files"{0}[Content]in  Navigation

the Source file Contains the Sharepoint side and the folder that we have filtered on

Parameter

let  
Parameter = #"Sample file" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type binary]in  Parameter

This is taking the Sample File(s) as the parameter

Parameters allow us to pass information or instructions into functions and procedures

Function

let  
Source = (Parameter as binary) => let  Source = Excel.Workbook(Parameter, null, true),  Navigation = Source{[Item = "IDB", Kind = "Sheet"]}[Data],  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),  
#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {All the columns})
in  #"Changed column type"in  Source

And the function applies the parameter as the source and then does all of our transformation steps

function is a block of organized, reusable code that is used to perform a single, related action

Power BI has done some of the hard work for us. We can continue to add steps to our transformation processes and so long as the format of all the files are the same we should be able to refresh the data and continue to combine new files from the Sharepoint folder

Power BI Connecting to a Sharepoint Document Library

I have some folders in Sharepoint and I want to be able to connect to these in Power BI

The folders are in

Get Data

There is no specific connector to Sharepoint Document Library at present

Choose Sharepoint Folder. Add in the Sharepoint Location. The route URL is required here

You may then need to authenticate your Sharepoint connection

When you get to the next screen notice that the content that is shown are files and we don’t want this. Click Edit

Simply Remove Files and change it to Contents

Click the tick and your contents will be refreshed to show Files

In this case click on Table against the Uploads folder which takes the user down to the next level.

Here are the 3 files that I want to append with all the data

Hopefully Power BI will introduce a better way of adding in Sharepoint Document Library soon

Create your website with WordPress.com
Get started