Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
useazebra
Advocate I
Advocate I

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

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

18 REPLIES 18

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.