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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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