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.