Combining 20 identical tables without using APPEND

Hi Mister_PowerBI ,

The obvious question is: Why don't you want to append them?

Appending isn't a particularly resource-intensive operation and, once combined and sent to the data model, this data structure will be significantly more efficient than the alternative.

However, there is an alternative.

-1- Create null-handled queries for each separate source. Chris Webb gives good examples of what I mean

here: https://blog.crossjoin.co.uk/2015/07/03/ensuring-columns-are-always-present-in-a-table-returned-by-power-query/ 

and here: https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/ 

Make sure to read the comments at the end of the posts as some users have contributed important updates to the blog process.

-2- Create dimension tables for all common dimensions and relate these to all 20 queries in the data model.

-3- Create query-specific measures against each query. For example:

sales_a = SUM(queryA[Sales])
sales_b = SUM(queryB[Sales])
sales_total = sales_a + sales_b
... etc.

-4- When visualising, ensure that you use the common dimension table fields to populate the visual, before adding query-specific fact measures. This should then work as required.

Unfortunately, you'll need to create many dimension tables, and 20 measures per fact calculation - 1 per query per calculation. You'll also have very inefficient compression due to the separated queries and the addition of all the dimension tables and subsequent relationships. As above, it's far more efficient to append.

Pete