Forum Discussion

hmokkapati's avatar
hmokkapati
Icon for Helper II rankHelper II
9 years ago
Solved

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

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...
Need Help
  • hmokkapati's avatar
    hmokkapati
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi hmokkapati,

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

Thanks,
Lydia Zhang

hmokkapati's avatar
hmokkapati
Icon for Helper II rankHelper II
9 years ago

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