Forum Discussion

useazebra's avatar
useazebra
Advocate 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(_)))
ImkeF's avatar
ImkeF
Community Champion
8 years ago

No, I don't think that other solutions will be better. you could have done it like in the link I've mentioned and maybe found your code easier to manage. The query evaluation would probably not change.

 

JeffWeir's avatar
JeffWeir
Advocate V
7 years ago

Hi ImkeF . There's a slight typo in your very helpful answer...you have prematurely closed off the List.Transform function with a bracket before the each keyword. It should be like this:

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

...and not like this:

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