Forum Discussion

smpa01's avatar
smpa01
Community Champion
9 years ago
Solved

Excel.Workbook (Multiple Files and Multiple Worksheets)

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.

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

    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

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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. :smileyhappy:

     

    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