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
4 years ago

Hi Ian_Mac2 ,
sure, no worries.
You have to modify Step3 like so:

Step3 = Table.SelectColumns(Step2, {"Custom", "AnotherColumnNameIWantToKeep"}),
Ian_Mac2's avatar
Ian_Mac2
Frequent Visitor
4 years ago

Hi ImkeF ,

I managed to play a bit with the code and realised that if the column needed is added to another step, it works.

So the one that seems to have worked is 
let
    Source = Folder.Files("E:\Project\TestFiles"),
    Step1 = Table.SelectColumns(Source,{"Content"}),
    Step2 = Table.AddColumn(Step1, "Custom", each Excel.Workbook([Content])),
    Step3 = Table.SelectColumns(Step2, {"Custom"}),
    Step4 = Table.ExpandTableColumn(Step3, "Custom", {"Name", "Data"}, {"Name", "Data"}),
    Step5 = Table.RemoveColumns(Step5 ,{"Name"}),
    Step6  = Table.AddColumn(Step6, "PromoteHeader", each Table.PromoteHeaders([Data])),
    Step7 = Table.SelectColumns(Step6,{"PromoteHeader","AnotherColumnNameIWantToKeep"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_)))),
    ExpandTable = Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable

Thank you very much for all the guidance!
Unfortunately, it shows just a blank column with the header Name.

It is very close, but I still cannot figure out how to populate it. Ideally, the excel files that get uploaded in the source folder should be combined, use the header of the first file and have the name in an additional column so it can be used for an unique ID.

Am I doing something wrong in the code?