A Troubled Union -- difficulties working with multiple data sources with identical formats

[khift363]

I am currently working on a report tracking items as they pass through fulfillment going from a set of shippers to a set of receivers and then ultimately to their final destination. All shippers have the same, standardized data format -- and the same with all receivers -- so operating a union across their collective data to turn them into one table is logically very easy after some basic formatting and filtering is done. The problem is, each shipper and receiver has their data on their own distinct servers, and as a result from what I'm reading PowerQuery does not appear to be able to execute a Union across these distinct data sources and still maintain the ability to fold queries. And to make matters worse, the data is simply too large to be able to practically import all of it with every import.

The data structure is as below. There's no logic or pattern to the shipments -- all shippers ship to all receivers, so there isn't a way to arrange that data without unioning them logically.

If DirectQuery is right out because it can't union across distinct sources, and full Import is out because the data is impractically large, is there some way to get Incremental Refresh set up for this data set even though the final table is not DirectQuery compatible? Each individual source is DirectQuery compatible -- can Incremental Refresh just query each source one at a time with a folded query including current date/time info, then do the union within Power BI? Or, alternatively and more brutally and surely worse performance -- can I just have each source exist as its own Incremental Refresh table within Power BI and do the unions in DAX on the report side? I'm sure the performance would be terrible, but I need something here.

Long term I really want to work with the folks in my company's tech department to try and see if logical views containing these individual buildings can be created for reporting services -- they're all under the same company umbrella and exist within the same system, just under different servers/instances -- but short term I need a report made sooner rather than later so I've got to work with the hand I have been dealt.

[Greg_Deckler · Accepted solution]

@khift363 Couple of thoughts, First, yes you could use UNION in DAX to create calculated tables. But, this would effectively kind of double the size of your data model which sounds like it would be bad in this case. You could potentially use Dataflows to each of the source systems. You could turn on the Advanced compute engine so that you could use DirectQuery with those dataflows. You can also turn on incremental refresh for dataflows (Premium, PPU, etc). Then perhaps you could either create a new dataflow that unions together the base dataflows or perhaps use DirectQuery to connect to the dataflows and then use Aggregation tables to aggregate the data. Or, you use the dataflows to create a datamart where you can union the tables together and such. Lots of possibilities I think if you start leveraging the features of the Service like datamarts, dataflows, etc.