Using a condition to select which column to use for merge

Power Query Data Setup
1

Hi, first time posting :slight_smile:

I am trying to merge two tables in Power Query but I wish to use a conditional join to select which column should be used to complete the join

This is the idea I am trying to get to work. Where the secondary table has “Yes” in the Locked column, merge to the main table using “hsObjectId” in the main table else use “Joining HubSpot ID for Accept / Billing” in the main table to merge by using “HubSpot ID” in the secondary table in both cases.

This is my attempting use and if to control the which column to use, did work of course

#"Merged Accepted Data" = Table.NestedJoin(
                        #"Merged Closed Data", 
                        if Table.SelectRows(#"Accepted Data",  each [Locked] = "Yes") then {"Joining HubSpot ID for Accept / Billing"} else {"hsObjectId"}, 
                        "Accepted Data",
                        {"HubSpot ID"}, 
                        "Accepted Data", 
                        JoinKind.LeftOuter),

can anyone assist? cheers

2

Hi @Charley194 ,

Welcome to the Forum.

Maybe you can first add custom column with this if then

= Table.AddColumn(#"Changed Type", "Custom_Merge_Column", each if [Locked] = "Yes" then [hsObjectId] else [#"Joining HubSpot ID for Accept / Billing"])

and then merge based on it ?

Best regards

3

How would I merge based on that return though?

4

If would like to merge data based on that new column you can use:

image
image682Ă—611 11.8 KB

This is for merge if you just want to create a join - you can do it also but keep in mind that in one table that column need to be unique

More about this functionality you can find at:
https://app.enterprisedna.co/app/player-course/8?chapter=322&item=1255
https://app.enterprisedna.co/app/player-course/8?chapter=322&item=1259

if you need something more do not hesitate to ask.

Best regars

5

Hello @Charley194

Did the response from @mspanic help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

6

Hello @Charley194

Just following up if the response above help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

We’ve noticed that no response was received from you on the post above. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @Charley194

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!