Retrieving all sheets from an excel file

[Anonymous]

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?

[Icey]

Hi @Anonymous ,

Are the replies above helpful?

Best Regards,

Icey

[Anonymous]

@Icey  yes sir. Sorry I forgot to mark it as solved

[Jimmy801 · Accepted 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

[Greg_Deckler]

@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?