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 ImkeF
I have a folder with multiple .xls file with same structure, it will change in future. I am trying to append all as a single file and but I don't need all the columns. I have around 40 columns and as per business we need only 20 columns. So I reffered your method of expanding the column dynamically. Below code works but it is too much slow.
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
ExpandTableFor testing i just had 1 .xls file of 1.5Mb in folder but it was too slow and in status bar it was showing loading more than 500Mb( clueless how ). So again I tried using single excel file with below code and it worked perfectly fine.
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
ExpandTableNow my question is:- 1. Why it is too slow for the first code? I am mising anything.
2. How can it be fixed.
Hope the above explanation gives you idea what I am trying to achive.
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
?
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
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 ExpandTableFor 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"?
