Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a worksheet updated each week. each week's data are on a different sheet. So every week a new sheet is added.
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
Solved! Go to Solution.
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... 😞
Proud to give back to the community!
Thank You!
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.
Regards,
Lydia
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.
Regards,
Lydia
wow , was so simple.
thx
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... 😞
Proud to give back to the community!
Thank You!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |