I have this issue of getting all sheets from an excel file. They all have the same treatment so I already wrote all steps. Right now I am duplicating the table and changing in the "Navigation" Step the name of the sheet. However I saw that there are roughly 120 sheets so this is going to take for ever.
Any idea on how to do that faster?
Solved! Go to Solution.
Hello @Anonymous
convert your query into a function in a way that it takes the sheet as input. Your code could look something like this
(YourSheet)=>
let
#"Promoted Headers" = Table.PromoteHeaders(YourSheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Component Hierarchy Task", Int64.Type}, {"Name", type text}})
in
#"Changed Type"
Then read your file (not a particular sheet) and add a new column where you apply your function above. Your code could look like this
let
Source = Excel.Workbook(File.Contents("YourExcelFile"), null, true),
#"Added Custom" = Table.AddColumn(Source, "YourSheet", each YourFunction([Data]))
in
#"Added Custom"
Now you have your sheets dynamically read in your new column and can expand it or whatever you need to do
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Anonymous ,
Are the replies above helpful?
Best Regards,
Icey
Hello @Anonymous
convert your query into a function in a way that it takes the sheet as input. Your code could look something like this
(YourSheet)=>
let
#"Promoted Headers" = Table.PromoteHeaders(YourSheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Component Hierarchy Task", Int64.Type}, {"Name", type text}})
in
#"Changed Type"
Then read your file (not a particular sheet) and add a new column where you apply your function above. Your code could look like this
let
Source = Excel.Workbook(File.Contents("YourExcelFile"), null, true),
#"Added Custom" = Table.AddColumn(Source, "YourSheet", each YourFunction([Data]))
in
#"Added Custom"
Now you have your sheets dynamically read in your new column and can expand it or whatever you need to do
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
@Anonymous - What if you did something like this:
let
Source = Excel.Workbook(File.Contents("C:\temp\powerbi\BearingInMind.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
Sheet4_Sheet = Source{[Item="Sheet4",Kind="Sheet"]}[Data],
Sheet5_Sheet = Source{[Item="Sheet5",Kind="Sheet"]}[Data],
Sheet6_Sheet = Source{[Item="Sheet6",Kind="Sheet"]}[Data],
Append = Table.Combine(Sheet1_Sheet, Sheet2_Sheet,Sheet3_Sheet,Sheet4_Sheet,Sheet5_Sheet,Sheet6_Sheet)
in
Append
You could do your transformations after the Append step.
@ImkeF @edhans is there any way to enumerate the sheets in an Excel spreadsheet similar to Source{0}[Contents] works to enumerate files in a folder?