Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Are the replies above helpful?

 

 

Best Regards,

Icey

Anonymous
Not applicable

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

Jimmy801
Community Champion
Community Champion

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
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.