Forum Discussion
How to insert rows from another table based on criteria.
This should be much easier if you just create a new table using DAX, you can add additional conditions for all columns ([BL No], [EQPID], [Vessel], [Voyage], [Bound]) with && Not in values() combination. See pbix if needed.
New Table =var notintable = CALCULATETABLE('Table (2)',FILTER('Table (2)',NOT([BL No] in VALUES('Table'[BL No])) && NOT([EQPID] in VALUES('Table'[EQPID]))))Return UNION('Table',notintable)
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
Hi Jihwan_Kim ,
Thank you for your response!
I cant send the pbix since the source is from our sql db not sure if it can be accessed outside.
Datasets in power query is not aligned like what I presented above though (ie. Table 1 does not have Load and Discharge columns in power query. I had to create lookupvalue from table 2 to align it).
It looked like that because I aligned the columns of tables 1 & 2 via DAX.
So I am hoping some dax can help out on table 1 too or maybe create a new calculated table but not sure how.
Thank you!
Hi,
Thank you for your feedback.
If you created the Load column and Discharge column, may I ask how do you get the Load and Discharge information for Voyage 202006?
I think, only Voyage 202007 's information is in Table2.
And in my opinion, it is better to create a new table by SQL, and then get the data from it into Power BI.
This is the equivalent of Table 2 in my original post. This is a list of all containers and their general route. The tricky part is that this list only shows the loading and final discharge port. It does not show the transshipment (if the container has been transferred to another vessel/voyage in a certain port hub).
This is the equivalent of Table 1 above. A more comprehensive tracer list of each container's movement.
The column portcode serves as the port hub where container is discharge (DCHT) on its first vessel and loaded (LODT) on to its next vessel.
Since Table 1 shows the original load port and its final discharge port and table 2 shows where the transfer happen,I created as :
Load = IF(ISBLANK(Transshipment[LODT]), LOOKUPVALUE('Total Slots + Repo'[LOADPORTID], 'Total Slots + Repo'[BL Number], Transshipment[BL Number]), Transshipment[portcode])
Discharge = IF(ISBLANK(Transshipment[DCHT]), LOOKUPVALUE('Total Slots + Repo'[DISCHARGEPORTID], 'Total Slots + Repo'[BL Number], Transshipment[BL Number]), Transshipment[portcode])
Hope this helps. Thank you so much for your time!