Expanding Multiple Data Tables with Unknown Column Names

Thank you Anonymous , very much appreciated.

You can try to buffer the list with the column names like so:

 DistinctColumn = List.Buffer(List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))))

Hi Anonymous ,

that's good to hear ;)

Buffering shall prevent the same expression unneccessarliy being evluated multiple times. Sometimes it works and sometimes not, so it's always worth trying out.

Hi Anonymous ,

I have some questions for you:

1) Why do you ignore the questions from my last post that would help me solve your problem?

2) Why do you pretend that your first codes worked if they couldn't? I have wasted my time with your faulty code and now you don't even bother to excuse for that. I've highlighted the corrections you've made below:

faulty code:
let
    Source = Excel.Workbook(File.Contents(""E:\Project\TestFiles\Data.xlsx"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "PromoteHeader", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(#"Removed Columns",Table.ColumnNames(#"Removed Columns"){0}), each Table.ColumnNames(_))), 
    ExpandTable =Table.ExpandTableColumn(#"Removed Columns","PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable 
	
correct code:	
let
    Source = Excel.Workbook(File.Contents("E:\Project\TestFiles\Data.xls"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "PromoteHeader", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.SelectColumns(#"Added Custom",{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(#"Removed Columns",Table.ColumnNames(#"Removed Columns"){0}), each Table.ColumnNames(_))), 
    ExpandTable =Table.ExpandTableColumn(#"Removed Columns","PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable	

faulty code:
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"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))),
    Step8 =Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    Step8


correct code:
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(Step4 ,{"Name"}),
    Step6  = Table.AddColumn(Step5, "PromoteHeader", each Table.PromoteHeaders([Data])),
    Step7 = Table.SelectColumns(Step6,{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))),
    ExpandTable = Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable	

?

Hi ImkeF 

I am really sorry for the mistake and didn't highlighted the change instead of placing the new code and resulting in loss of your valuable time. I posted first with different system so had to write the code manaully by seeing the code and I may overlooked at few points and after submit it showed an error and I had to rewrite entired post again and caused wrong code. This is my first attempt to post in the forum and i I really sorry for my error in code and your time as well. I will be very careful in posting the code again.

Hi ImkeF 

It solved my issue than you for your guidance :) , just want to understand what this code actually did? Did it forced to do calculation at Distinct column?

Hi ImkeF ,

I apologise for resurecting this thread. I would like to ask you if there is a possibility to preserve the name of the file we are importing as well. 

Step5 = Table.RemoveColumns(Step4 ,{"Name"}),
    Step6  = Table.AddColumn(Step5, "PromoteHeader", each Table.PromoteHeaders([Data])),
    Step7 = Table.SelectColumns(Step6,{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))),
    ExpandTable = Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable	

For example, the code you wrote here earlier outputs a new table all together and loses the column "Name" that we had in Step 5. Is there a possibility to add it to the outputed Excel Data as a new column with the header "Name"?