Expanding Multiple Data Tables with Unknown Column Names

[useazebra]

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). 

Dynamic sourcing works perfectly. This code always brings in this exact table:

     Source = Web.Page(Web.Contents(GetValue("Primary_Table"))),
     #"Filtered Rows" = Table.SelectRows(Source, each ([Caption] = null)),

 

Auto-generated code for expanding the two tables in the "Data" column uses static names of sub-tables' column headers:

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Fiscal year is January-December. All values USD Millions.", "31-Dec-2017", "30-Sep-2017", "30-Jun-2017", "31-Mar-2017", "31-Dec-2016", "5-qtr trend", "All values USD Millions."}),

THE PROBLEM

I'm trying to make my code generic, so it will work even when the headers on the sub-tables change (and they do). My thinking is that I need a function to list out all the column headers down in those two tables, like this:

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(#"Filtered Rows"[Data])),

That code gives me an error:  "We cannot convert a value of type Table to type List"

I tried adding {} around the [Data] reference at the end of the line:

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(#"Filtered Rows"[Data])),

Which gives the error: "There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

How do I make this code properly expand both sub-tables in a way that works even if the headers change? 

[ImkeF]

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

Step3 = Table.SelectColumns(Step2, {"Custom", "AnotherColumnNameIWantToKeep"}),

[Ian_Mac2]

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?

[Greg_Deckler]

I've seen @ImkeF answer similar problems to this.

[ImkeF · Accepted solution]

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

[useazebra]

Thanks for your help! I had to move one ")" so the code now reads: 

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data], each Table.ColumnNames(_))))

Question: You said "If you want to include it in one step..." Do you have a suggestion on a better way to do this, perhaps in multiple steps?

[Anonymous]

Thank you for this solution and it works perfectly fine if we use get data from excel but if we get data from Folder then this run too much slow. I have a folder with 1 excel ( just for testing i kept 1 but there will be many files) and when i applied this approach then it was too slow when checked the status bar then found that it was loading more than 500Mb of data where as my file size is only 1Mb. Can you please help me how to solve this ( i know we can use Table.Combine to achive but that doesn't serve the purpose).

[ImkeF]

Hi @Anonymous ,

not sure I can follow what you're describing here. Please post your M-code so that I can follow.

[Anonymous]

Below code works fine if I load data from excel i.e. selecting excel Data.xlsx

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 

But it is too much slow at last line if I load data from folder( in status bar it shows loding more than 500Mb data where as i have only 1 file in folder with size 1Mb. I have tried using Table.Buffer as well but it also runs 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(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

[ImkeF]

Hi @Anonymous ,

your code doesn't work for me. Please check if you have added or removed some steps here before pasting into the thread.

What are you trying to achieve?:

1.  Dynamically retrieve the names of the first file and apply that to all files of the folder or
2.  Dynamically retrieve the names of ALL files in the folder and append all tables (with respective null values for columns that are not present in all tables)
3.  

[Anonymous]

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
    ExpandTable

For 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
    ExpandTable

Now 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.

[ImkeF]

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	

?

[Ian_Mac2]

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"?

[Anonymous]

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.

[ImkeF]

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

[Anonymous]

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?

[ImkeF]

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.

[ImkeF]

No, I don't think that other solutions will be better. you could have done it like in the link I've mentioned and maybe found your code easier to manage. The query evaluation would probably not change.

[JeffWeir]

Hi @ImkeF . There's a slight typo in your very helpful answer...you have prematurely closed off the List.Transform function with a bracket before the each keyword. It should be like this:

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data], each Table.ColumnNames(_))))

...and not like this:

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data]), each Table.ColumnNames(_)))