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

 

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?

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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