TABLE.EXPANDTABLECOLUMN issue - does not work as expected when new column is added in data

[hmokkapati]

Hello,

 I have a source excel with week as headers. Every week a new column is added. So when Power BI Expands this table in the below code, the column names are hardcoded. Instead is there a way that the newly added column can be automatically taken care in the "Expanded Data" step and I do not have to hardcode the column numbers. 

 #"Removed Other Columns2" = Table.SelectColumns(#"Expanded Custom",{"Data"}),
 #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns2", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", AND SO ON ............................."Column197", "Column198", "Column199", "Column200", "Column201", "Column202"}),

Appreciate any pointers

Thank You,

Harisha

[Anonymous]

Hi @hmokkapati,

Could you please share  me the source Excel file so that I can reproduce?

Thanks,
Lydia Zhang

[hmokkapati · Accepted solution]

Hello Lydia,

This has been solved. I actually rewrote the code as below to open the excel in a different way instead of doing ExpandTableColumns which would list down all the columns

//Fetch the file from Sharepoint Location
    Source = SharePoint.Files("<path of the Sharepoint folder>", [ApiVersion = 15]),
    #"Filtered Rows8" = Table.SelectRows(Source, each [Folder Path] = "<folder path>"),
    #"Filtered Rows5" = Table.SelectRows(#"Filtered Rows8", each ([Name] = "<file name>")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows5",{"Content"}),

//code to expand the Data irrespective of column names
    #"Sheet1" = Excel.Workbook(#"Removed Other Columns"{0}[Content], null, true),
    #"Sheet1_Sheet" = #"Sheet1"{[Item="Sheet1",Kind="Sheet"]}[Data],

in #"Sheet1_Sheet"

Thanks for offering to help.

Cheers,

Harisha