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(_)))
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.
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 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?