How to insert rows from another table based on criteria.

[dnsia]

Hi all,

Please help.

I have 2 tables. I need to insert data rows in table 1 from table 2 based on conditions.

If table 2 has the same [BL No], [EQPID], [Vessel], [Voyage], [Bound] then it should not be inserted in table 1 else, proceed to insert rows.

Table 1.

BL No

EQPID

Load

Discharge

Vessel

Voyage

Bound

BL002

Container 2

PORT 2

PORT 3

VES 2

202006

S

BL001

Container 1

PORT 2

PORT 3

VES 2

202006

S

BL002

Container 2

PORT 1

PORT 2

VES 1

202007

N

BL001

Container 1

PORT 1

PORT 2

VES 1

202007

N

Table 2. 

BL No

EQPID

Load

Discharge

Vessel

Voyage

Bound

BL002

Container 2

PORT 1

PORT 3

VES 1

202007

N

BL001

Container 1

PORT 1

PORT 3

VES 1

202007

N

BL003

Container 3

PORT 1

PORT 4

VES 1

202015

N

BL004

Container 4

PORT 1

PORT 4

VES 1

202015

N

Desired Output for Table 1:  

BL No

EQPID

Load

Discharge

Vessel

Voyage

Bound

BL002

Container 2

PORT 2

PORT 3

VES 2

202006

S

BL001

Container 1

PORT 2

PORT 3

VES 2

202006

S

BL002

Container 2

PORT 1

PORT 2

VES 1

202007

N

BL001

Container 1

PORT 1

PORT 2

VES 1

202007

N

BL003

Container 3

PORT 1

PORT 4

VES 1

202015

N

BL004

Container 4

PORT 1

PORT 4

VES 1

202015

N

Thank you! 

[Anonymous · Accepted solution]

@dnsia 

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.

[Jihwan_Kim]

Hi, @dnsia 

Please correct me if I wrongly understood your question.

In my opinion, this can be done in Power Query Editor by using APPEND + remove duplicate.

If you have a sample pbix file, please delete important information and share the link here, then I can try to look into it to come up with a more accurate solution.

Thank you.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[dnsia]

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!

[Jihwan_Kim]

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.

[dnsia]

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!