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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
IvoryKoss
Frequent Visitor

select automatically all the sheets of an excel file

I have a worksheet updated each week. each week's data are on a different sheet. So every week a new sheet is added.

Capture2.PNG

1- I want  to automatically select all the sheets without me having to select sheet per sheet. So every time a new sheet will be added, the data will be updated in my model.

 

2-I want the sheet names (i.e Week28) to be filled as a column data for a new column "WEEK "

 

PLease help

 

2 ACCEPTED SOLUTIONS
fhill
Resident Rockstar
Resident Rockstar

I'm not sure about question 1, but this is an easy way to take care of part 2...

 

Under Advanced Editor, you can add this type of command to create a column matching the 'Name' of the sheet...  Custom is the desired column name, and 'Report

Insert Sheet Name = Table.AddColumn(#"Changed Type", "Custom", each Source{[Item="Report",Kind="Sheet"]}[Name])

 

Thinking about it more, this would kinda suck, as you would have to define the ''Report" name each time, by then you could just add a Custom Coulmn of the name itself..  😞    Well, I hope this helps take you one steps closer to a solution...  😞

 

Capture.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




View solution in original post

v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@IvoryKoss,

Do you have same table structure for the data in each worksheet and want to combine the data in each worksheet in a single table? If so, open Power BI Desktop, and choose “Get Data->Blank query”, then click Advanced Editor and paste the following code in it.

let
    FullFilePath = "Yourfilepath\Excelfilename.xls",
    Source = Excel.Workbook(File.Contents(FullFilePath))
in
    Source

 

Then you can expand Data column to get detailed data of each worksheet. And each time a new worksheet is added to your excel file, click “Refresh Preview” button, Power BI Desktop will bring you the data of new worksheet.
1.PNG

 

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@IvoryKoss,

Do you have same table structure for the data in each worksheet and want to combine the data in each worksheet in a single table? If so, open Power BI Desktop, and choose “Get Data->Blank query”, then click Advanced Editor and paste the following code in it.

let
    FullFilePath = "Yourfilepath\Excelfilename.xls",
    Source = Excel.Workbook(File.Contents(FullFilePath))
in
    Source

 

Then you can expand Data column to get detailed data of each worksheet. And each time a new worksheet is added to your excel file, click “Refresh Preview” button, Power BI Desktop will bring you the data of new worksheet.
1.PNG

 

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

wow , was so simple.

thx

fhill
Resident Rockstar
Resident Rockstar

I'm not sure about question 1, but this is an easy way to take care of part 2...

 

Under Advanced Editor, you can add this type of command to create a column matching the 'Name' of the sheet...  Custom is the desired column name, and 'Report

Insert Sheet Name = Table.AddColumn(#"Changed Type", "Custom", each Source{[Item="Report",Kind="Sheet"]}[Name])

 

Thinking about it more, this would kinda suck, as you would have to define the ''Report" name each time, by then you could just add a Custom Coulmn of the name itself..  😞    Well, I hope this helps take you one steps closer to a solution...  😞

 

Capture.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.