Loading files from a SharePoint folder

Option 1: link to the file

• Go to the SharePoint folder and select -not open- the file you want to load

• Open the file in the SharePoint app (desktop)

  

• In the Excel File: File > Info > Copy file path

• In Power BI desktop: Get Data > Web

• Paste the copied 'file path' in the previous step into the URL field

• Delete ?web=1 from the end of the URL > Accept

• Sign in into your organizational MS365 account > Connect

• Select sheet(s)/table(s) from the file > Load or Transform Data

Option 2: link to the folder

• Go to the SharePoint folder and copy the URL folder (no sub-libraries) https://mycompany.sharepoint.com/sites/MySite/
• In Power BI desktop: Get Data > SharePoint folder > Connect > Paste copied URL > Accept
• Sign in into your organizational MS365 account > Connect > Transform Data
• In Power Query editor:

  • Go to the created query/table

  • Filter the [Folder Path] according the folder(s) you are interested in.

  • Filter the [Name] column to include the file you want to load.

  • Add Column > Custom Column Name: File; Formula: = Excel.Workbook([Content], true)

  • Go to the newly created column [File] and click over 'Table'. It will expand all the sheets/tables from the file:

  • Go to the [Data] column and click again over 'Table' in the row you are interested in. this will expand the selected sheet/table:

    

Combining files from a SharePoint folder

• To combine files -with the same structure- from a SharePoint folder in Power BI, follow these steps:

  • Go to the SharePoint folder and copy the main link (no sub-libraries) (e.g., https://mycompany.sharepoint.com/sites/MySite).
  • In Power BI: Get Data > SharePoint folder.
  • Paste the copied address into the URL field and click Transform.
  • In Power Query:
    • Go to the created table.
    • Go to the Source step.
    • Change SharePoint.Files to SharePoint.Contents in the formula bar.
    • Find the row where [Name] = "Shared Documents" and click "Table" in the [Content] column.
    • Repeat the previous step for each folder containing the files you want to combine.
    • Combine the files using the "Combine Files" window that appears when you expand the [Content] column.
    • Rename the query/table to SharePointFolder to make it easier to reference in the future.