Power BI – Importing Multiple Excel Sheets

Last week, I received an Excel file with hundreds of sheets and I had to import it to Power BI. The Excel spreadsheet had one sheet per day, as below:

When I tried to import it, I realized that a lot of people may have the same requirement and I decided to write this post, which I will show two ways to bring all sheets in Power BI.

Importing using Code

• In Power BI, go to “Home” > “Edit Queries”

•  In “Edit Queries”, go to “New Source” > “Blank Query”

• Once you click on “Blank Query”, it will display the screen below, then click on “Advanced Editor”

• In “Advanced Editor” type the code below, replacing file path and file name then click “Done”:

let
    Source = Excel.Workbook(File.Contents("<File Path>\<File Name>.xlsx"), null, true)
in
    Source

• In the screen below filter to show ONLY “Sheet”.

• In the next step, you need to remove all columns, except the column “Data”. Right-click on “Data” and click on “Remove Other Columns”

• Click on expand icon and click “OK”, as below:

• Click on “Use First Row as Headers”

• Click on Filter “Date” and untick “Date” (this step is to remove all Headers from other sheets).

Importing using Wizard

• Open Power BI, go to “Get Data” > “All” > “Folder” > “Connect”

• Type the folder path where you saved your spreadsheet

Note: Ideally, your folder should have only the spreadsheet you want to import to Power BI, otherwise you have to filter in the edit query.

• Click on “Combine” > “Combine & Edit”

• Click on “Sample File Parameter” > “OK”

• In the screen below filter to show ONLY “Sheet”.

• In the next step, you need to remove all columns, except the column “Data” Right-click on “Data” and click on “Remove Other Columns”.

• Click on expand icon and click “OK”, as below:

• Click on “Use First Row as Headers”

• Click on Filter “Date” and untick “Date” (this step is to remove all Headers from other sheets).

Hope it helps. 🙂