Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Retrieving all sheets from an excel file

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?

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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?

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Are the replies above helpful?

     

     

    Best Regards,

    Icey

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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