Conditional Merge in Power Query

[Mic1979]

Dear all,

I have an issue in Power Query. 

I don't know how to finalize a Table.NestedJoin between two tables with a condition. I tried to do in this way:

 #"Model_To_Costify STEPS" = Table.AddColumn(#"Expanded Manual_Override_SubAssy", "Custom",
each if [Product Type Description] = "Pilot Valve"
then Table.NestedJoin({"New Product MNR"}, Model_To_Costify_VALVE, {"New Product MNR"}, "Model_To_Costify_VALVE", JoinKind.LeftOuter)
else "")

but this is not working.

Could you support me?

Many thanks in advance.

[Mic1979]

Hello,

just to be clearer:

1. I have in my Destination Table a column named "Product Type Description" with 3 possibile values: Pilot Valve, Pilot without Coil, SBSA
2. Based on the value in  "Product Type Description" I want to merge the Destination Table with 3 different Source Table: Model_To_Costify_VALVE, Model_To_Costify_SBSA
3. Then I will expand the result of the Merge

Do you think this will be possible?

Thanks.

[PawelWrona]

Yes, you must just expand my "query view" to your specific scenario. Then you would have the following:

So, the idea is the same, split Destination Query into 3 subsequent queries, based on the value in Project Type Description. Perform 3 separate merges and expand the results. Append all 3 queries into single query at the end and this one you should load to your model. Disable load on the rest.

[Mic1979]

Got it and many thanks for this explanation.

Just a question: is it not possible to use an if...then else with a condition based on the value in "Product Type description" Column"?

[PawelWrona]

There is, but the solution would be more complicated. You wouldn't use the NestedJoin, but it would be more like "vlookup" solution, which is quite complicated in Power Query. What is even worse, you would have to apply it for each column separately, that might be even more complicated in the end.

If you would like to see how you could do something like "vlookup" in Power Query I recommend this video:

Nested functions and Table.Buffer() (youtube.com)

[PawelWrona]

Why do you need a condition with Join? What would be requirement here?

Quick answer is that you could connect to your table, reference it twice to create two new queries. Filter them based on the condition = tru and condition = false. The one where condition = true perform the merge, and append both queries togother in the end. Remember to disable load on all the queries except the last one:

[Greg_Deckler]

@Mic1979 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.