cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
useazebra Frequent Visitor
Frequent Visitor

Expanding Multiple Data Tables with Unknown Column Names

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? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

15 REPLIES 15
Super User
Super User

Re: Expanding Multiple Data Tables with Unknown Column Names

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

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

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

useazebra Frequent Visitor
Frequent Visitor

Re: Expanding Multiple Data Tables with Unknown Column Names

 

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?

 

 

Super User
Super User

Re: Expanding Multiple Data Tables with Unknown Column Names

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.

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




JeffWeir Regular Visitor
Regular Visitor

Re: Expanding Multiple Data Tables with Unknown Column Names

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

 

mukeshkumar3010 Frequent Visitor
Frequent Visitor

Re: Expanding Multiple Data Tables with Unknown Column Names

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

Super User
Super User

Re: Expanding Multiple Data Tables with Unknown Column Names

Hi @mukeshkumar3010 ,

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

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mukeshkumar3010 Frequent Visitor
Frequent Visitor

Re: Expanding Multiple Data Tables with Unknown Column Names

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

 

Super User
Super User

Re: Expanding Multiple Data Tables with Unknown Column Names

Hi @mukeshkumar3010 ,

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.  

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)