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:

Sheet_150717

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”

Code_Pic1_150717

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

Code_Pic2_150717

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

Code_Pic3_150717

  • 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

Code_Pic4_150717

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

Code_Pic5_150717

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

Code_Pic6_150717

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

Code_Pic7_150717

  • Click on “Use First Row as Headers”

Code_Pic8_150717

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

Code_Pic9_150717

Importing using Wizard

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

Wizard_Pic1_150717

  • 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.

Wizard_Pic2_150717

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

Wizard_Pic3_150717

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

Wizard_Pic4_150717

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

Wizard_Pic5_150717

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

Wizard_Pic6_150717

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

Wizard_Pic7_150717

  • Click on “Use First Row as Headers”

Wizard_Pic8_150717

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

Wizard_Pic9_150717

Hope it helps. 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: