Rename columns of nested tables in Power Query

Typical scenario

You have several data sources – Table1, Table2 and Table3

You want to combine data


Unfortunately, column names are different in those data sources, but structure is similar.

One of possible solutions is to create three separate queries, apply necessary transformations there and combine them after all.

And this would be fine solution. Moreover, it is only possible solution when function of data extraction is different for each data source.

Scalability: when we get another source, e.g. Table4, we will create fourth query and add it in Table.Combine() in addition to previous three.

But! We can slightly simplify scalability process when same extraction function used for each data source.

Extend our model with “Rename Table” and “Control Table”.


“Rename Table” contains renaming rules – list of old names and corresponding new names, expected in resulting table.

“Control Table” contains list of data sources, names or URLs or something else, which defines data source and can be used as input for extraction function.

Use Table.AddColumn() to apply extraction function to each row of “Control Table”


= Table.AddColumn(#”Changed Type”, “Custom”, each Excel.CurrentWorkbook(){ [ Name= [Source] ] } [Content])

In this simple example I take data from same workbook and Table1, Table2, Table3 are names of tables with data.

We get three nested tables and may see in preview area that column names are different


 

 

 

If simply press “Expand” button it offers only column names from first nested table


If agree with such expand we get wrong data for Table2 and Table3


Not what we expected.

We can successfully expand all nested tables if only rename columns before expand operation.

I take renaming command from one of my previous posts – Dynamic Table Headers in Power Query

Table.RenameColumns( TARGET_TABLE, Table.ToColumns(Table.Transpose( RENAME_TABLE )), MissingField.Ignore)

We must apply this command to each row of column with nested tables. It is called Transformation in PQ and function Table.TransformColumns serves this purpose.

Table.TransformColumns( #”Added Custom”, { {“Custom”, each any_function } } )

Let’s replace “any_function” with our renaming command

Table.TransformColumns( #”Added Custom”, { {“Custom”, each Table.RenameColumns( _, Table.ToColumns(Table.Transpose( RENAME_TABLE )), MissingField.Ignore) } } )

TARGET_TABLE in this case is a value of cell in each row of column “Custom”, therefore replaced with underscore character.

Check preview of Table3 – expected new names of columns


Finally, we get desired result


Why do I have column “Filter” you may ask?

Would be great to filter source table before union. It can be done after union, because in resulting table we have column Source.

But I would like to filter nested tables using criteria from “Control Table”.

Unfortunately, I couldn’t find a way to do this.

I expected that same

Table.TransformColumns( #”Added Custom”, { {“Custom”, each any_function } } )

Will do this work with “any_function” as

Table.SelectRows( _, Expression.Evaluate( “each ” & [Filter] ) )

However, PQ doesn’t recognize [Filter] column


Seems it works like Filter Context in DAX. When PQ Engine runs Table.SelectRows() it doesn’t see data outside of target table.

Nevertheless, if you have different logic of criteria generation you still can use this pattern

= Table.TransformColumns( #”Added Custom”, { {“Custom”, each Table.SelectRows( _, Expression.Evaluate( “each [Amount] > 10” ) ) } } )


Generate string with criteria and then use Expression.Evaluate().

Sample workbook can be downloaded from here

Real life example

Described approach works fine when I get data from multiple SAP Business ByDesign tenants using OData


Such “Control Table” allows to build connection string for each row and then use function ByD.GetReportDataIDs.

Then described renaming technic can be applied to nested tables using “Rename Table”


Using such “Control Table” I can easily extend my model to any other SAP ByD tenant even if I have extension fields there.

Since last update of Power BI Desktop it allows to store static tables in .pbix file that makes execution of such scenario easier.

More details about this approach in future posts.

Advertisements

5 thoughts on “Rename columns of nested tables in Power Query

  1. ImkeF 2016-01-25 / 21:28

    Hi Ivan,
    this is so cool – can’t believe! Finally got around how Expression.Evaluate works. Possibilities are limitless:

    You can apply the filter before expanding like this – wrapping the filter expression around the selection:

    #”Added Custom” = Table.AddColumn(#”Changed Type”, “Custom”, each Table.SelectRows(Excel.CurrentWorkbook(){ [ Name= [Source] ] } [Content], Expression.Evaluate(“each “& [Filter]))),

  2. Ehren 2016-03-09 / 01:48

    Ivan, great post. Lookup tables like these were one of the prime scenarios we had in mind when we decided to keep Expression.Evaluate as part of the M formula library.

  3. ivan 2017-03-07 / 00:32

    In fact, PQ Engine does see data outside of target table.You need to use this syntax:

    Table.SelectRows( _, Expression.Evaluate( “each ” & #”Added Custom”{_}[Filter] ) )

    Учите матчасть )

    • Ivan 2017-03-07 / 09:02

      Sory, was wrong. “#”Added Custom”{_}[Filter]” will not work here. But, maybe not because of “Table.SelectRows”, but because of “Table.TransformColumns”.

      Thank you for the usefull article.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s