Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |