Forum Discussion
Expanding Multiple Data Tables with Unknown Column Names
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(_)))
I've seen ImkeF answer similar problems to this.
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(_)))
Thanks for your help! I had to move one ")" so the code now reads:
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data], each Table.ColumnNames(_))))
Question: You said "If you want to include it in one step..." Do you have a suggestion on a better way to do this, perhaps in multiple steps?
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.
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(_)))
Thank you for this solution and it works perfectly fine if we use get data from excel but if we get data from Folder then this run too much slow. I have a folder with 1 excel ( just for testing i kept 1 but there will be many files) and when i applied this approach then it was too slow when checked the status bar then found that it was loading more than 500Mb of data where as my file size is only 1Mb. Can you please help me how to solve this ( i know we can use Table.Combine to achive but that doesn't serve the purpose).
Hi Anonymous ,
not sure I can follow what you're describing here. Please post your M-code so that I can follow.
