select automatically all the sheets of an excel file

[IvoryKoss]

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

[fhill · Accepted 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...  😞

[v-yuezhe-msft · Accepted solution]

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

Regards,
Lydia

[IvoryKoss]

wow , was so simple.

thx

[fhill · Accepted 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...  😞