Combining 20 identical tables without using APPEND

[Mister_PowerBI]

Hello, 

I have a client who uses software that creates the exact same database structure for each of their legal entities. In total, there are 20 databases. I want to combine data from these 20 databases into ONE Power BI table without using append. 

I have created a Power Query function that loops through each database and pulls the data, next a I can extract the data. The only thing is that in the Power BI Service, I can't get this to work. The service constantly wants me to specify a datasource with a 'null' database, but it's not allowed to leave this field open.

Concluding: in Power BI Desktop this is working fine but Power BI Service is giving me a lot of trouble. Is there an easier way to combine this data in Power BI without appending 20 tables? 

[BA_Pete]

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

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