Setup PowerBI to refresh from a OneDrive folder

See the tip Setup PowerBI to refresh from OneDrive for details of setting a connection to a single file.

To set-up a connection to a OneDrive folder

Create the report in PowerBI

On the Home menu select Get Data - SharePoint folder

Enter the base Url for the OneDrive site.

It has the format of **https://YourDestinationTenantDomain-my.sharepoint.com/personal/**YourUserName_ YourO365Domain/

Click on the OK button.

A list of files will be displayed. Click on the Transform button.

In the Formula bar change the SharePoint.File to SharePoint.Contents

Drill-down on Table in the Content column where the Name column is Documents.

Continue drilling down on Table in the Content column where the Name column contains the sub-folder name, until you reach the sub-folder that you want to consolidate.

With the sub-folder open and the files that you want to consolidate displayed click on the Combine Files icon in the Content column header.

Choose the worksheet holding the data that you to consolidate.

Click on the OK button.

Preview data will be returned. Add any further steps required to transform the data. Click on Close & Apply

Sample Code:

let
Source = SharePoint.Contents("https://cloud-my.sharepoint.com/personal/rob/",[ApiVersion = 15]),
Documents = Source{[Name="Documents"]}[Content],
#"Hyp view" = Documents{[Name="Hyp view"]}[Content],
#"Max" = #"Hyp view"{[Name="Max"]}[Content],
#"Data" = Max{[Name="Data"]}[Content],
#"Data - Values" = Data{[Name="Data - Values"]}[Content],
#"Filtered Rows" = Table.SelectRows(#"Data - Values", each [Extension] = ".xlsx"),