Expand table with dynamic list

[Anonymous]

Hi,

I have a step where I create a Custom List1 utilizing the equation:

=  List.Union(List.Transform(#"Removed Other Columns1"[Data], each Table.ColumnNames(_)))

This give me a list of all the column names in all files (50+) with no duplicates. 

My next step I want to expand a table utilizing the list in the Custom List1, but I'm not sure how to call the list. This is definitely wrong, but here is what I used:

= Table.ExpandTableColumn(#"Removed Other Columns1", "Data", List.Union(#"CustomList1"[List]))

Please let me know if there is a solution!

[camargos88 · Accepted solution]

Hi  @Anonymous ,

Try this mcode:

let
Source = Table.FromRecords({
[Age = 1, Name = "Bob", Phone = "123-4567", Data = Table.FromList({[Age = 10, ID = "10"],[Age = 20, ID = "20"]}, Record.FieldValues,{"Age", "ID"})],
[Age = 2, Name = "Jim", Phone = "987-6543", Data = Table.FromList({[Age = 20, Email = "Bob@gmail.com"],[Age = 30, Email = "Jim@gmail.com"]}, Record.FieldValues,{"Age", "Email"})],
[Age = 3, Name = "Paul", Phone = "543-7890", Data = Table.FromList({[Age = 30, LastName = "Bob"],[Age = 40, LastName = "Jim"]}, Record.FieldValues,{"Age", "LastName"})]
}),
ListColumns = List.Distinct(List.Union(List.Transform(Source[Data], each Table.ColumnNames(_)))),
Expand = Table.ExpandTableColumn(Source, "Data", ListColumns, List.Transform(ListColumns, each _ & ".1"))
in
Expand