Forum Discussion

useazebra's avatar
useazebra
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Expanding Multiple Data Tables with Unknown Column Names

I'm creating a query which the user can easily point to an alternate source without having the query break. All source tables are highly standardized EXCEPT for the column headers (go figure).    D...
Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    True :)

    Your syntax would only work if you would reference a column that contains the column names already like here for example: https://www.mrexcel.com/forum/power-bi/952568-power-query-expand-all-columns.html

     

    But in your case you have to include the Table.ColumnNames function to retrieve the column names first. If you want to include it into one step, it could look like so:

     

    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data]), each Table.ColumnNames(_)))
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

I've seen ImkeF answer similar problems to this.

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
8 years ago

True :)

Your syntax would only work if you would reference a column that contains the column names already like here for example: https://www.mrexcel.com/forum/power-bi/952568-power-query-expand-all-columns.html

 

But in your case you have to include the Table.ColumnNames function to retrieve the column names first. If you want to include it into one step, it could look like so:

 

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data]), each Table.ColumnNames(_)))