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

 

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

2 Replies

  • 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