Forum Discussion
How would you model this ?
How would you go about in modelling this requirement on power query or on dax
1 Import Dataset coming in every (A) 4 hours averaging 50 rows with city as primary field, need to store this in Power BI for a weeks data.
1 Direct Query Dataset (B) averaging 50 rows with city as primary field
1 Import Dataset coming in daily (C) averaging 8 million rows with city as primary field , this will go with the dataset B refresh.
There is more too but this will give a base.
Data from A,B,C all will go into the same report.
Merging A (Left join) to B (Left join) to C will blow up the dataset when merged as new on power query if am not wrong
I would still prefer Power Query given this could be still be optimized.
Or merge A to B resulting AB and then join with C on the data model on DAX ?
Left outer is not a graphical join on DAX data model or a full outer that may also be required, could do DAX to create those tables but looking for simpler and optimal ways out here.
Shout out to the experts for Best practices here ...
curiouspbix0 Yes sir
9 Replies
curiouspbix0 , did not get it completely, but if it import or mixed model, you should able to create tables using
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
In DAX we use +0, or Show item without data for left join.
My question is more inline to an optimal model for the req at hand.
Not on how to do it literally on Power Query actually.
1. Join master tables on left to the transacational table on right with huge volume to write a new dataset on power query.
2. Keep the master table seperate to transactional table in power query and later join them in DAX data model ?
3. Other optimal options ?
Any inputs from the experts ?
curiouspbix0 I don't see any reason you need to merge in power query, it is typical dim and fact model.
Only thing I'm not clear what is the difference between A and B since both have City data?
If these two have different cities, I would recommend to append this in power query and make one city dimension and make sure it is unique (remove duplicate values)
From here everything will be super easy to work with and you will have the most effective model.
I hope it helps.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
