Excel.Workbook (Multiple Files and Multiple Worksheets)

[smpa01]

I know that Excel.Workbook can be used to tell POWER BI to pickup an excel file/ multiple excel files from a particular folder. For example, there is a folder called POWER BI and it contains a Excel files named A. This file have single workbook Sheet 1 and when I use Excel.Workbook, Power BI nicely brings the data set from the excel file to the the Query Editor. However, I have noticed that if the Excel file A contains multiple worksheets (Sheet 1, Sheet 2, Sheet 3) and the required data set is in Sheet 3, Excel.Workbook always picks up data from Sheet 1 and does not pay attaention to other sheets. Is there a way to specify the sheet name.

Also, if I have two excel files A and B in the folder Power BI and Sheet 3 of A and Sheet 1 of B contains the required data set. Can I use Excel.Workbook to bind this two sheets together. I know it works perfectly well if both the files have data in Sheet 1.

Thank you in advance.

[v-ljerr-msft · Accepted solution]

Hi @smpa01,

Based on my test, you should be able to use the formula below to specify which sheet you want to import from an excel file. 

let
    Source = Excel.Workbook(File.Contents("C:\Power BI\DateFomat.xlsx"), null, true),
    
    //specify which sheet you want to import
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date2", type date}})
in
    #"Changed Type"

Regards