*(why the heck would you want to do that? just access the original data)
Because I need to further enrich and shape the data that comes from more then one Dataset. I I may not have access to the original data.
In my case, I have Inventory data from 3 sources, each organized slightly differently. (I.E. On Hand is in one Dataset, Distributor Inventory is in another, and Purch Orders in a 3rd. I make Reports for Operations and also for Finance, and they each define what they consider to be "Inventory" differntly. Like, Finance counts POs that have not been recieved yet as Inventory on the books, but, Operations would not consider that since they can't use it in production yet).
If I was able to get all 3 Datasets into PowerQuery, I would do my merges and appends there, and end up with a nice neat Fact table, perhaps with a column called 'source', so I could categorize my Inventory, either in PQ or DAX. I envision a series of if statements, like "FinInv = if source is Dataset 1 or Dataset 3 then 'Dataset 1' [Qty] + 'Dataset 3" [Qty] else 0" and then "OpsInv = if source is Dataset 1 then 'Dataset 1 [Qty] else 0"
When I try to append them in DAX, I hit a 1Mill row limit pretty quick
