Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
93 | |
88 | |
35 | |
35 |
User | Count |
---|---|
154 | |
101 | |
80 | |
63 | |
54 |