Conditional Merge

[Anonymous]

ello i have a query wich i am working on and I need to merge it depending on the value of [ID RS] if its certain values then merge it with a table called CCunicos and the other [ID RS] with another table called CONTRATOS using also another identifier named [ID_LOC]

my code looks like this:

Let 

....

(all the steps in the query)

.....

#"Custom"= if [ID RS]=39 or [ID RS]=41 or [ID RS]=46 or [ID RS]=48 or [ID RS]=55 or [ID RS]=56 or [ID RS]=58 or [ID RS]=70 or [ID RS]=71 or [ID RS]=74 or [ID RS]=75 or [ID RS]=83 or [ID RS]=88 or [ID RS]=91 then ( Table.NestedJoin(#"Columnas reordenadas16","ID RS",CCunicos,"ID RS","Prueba",JoinKind.LeftOuter))

else ( Table.NestedJoin(#"Columnas reordenadas16",{"ID RS", "ID_LOC"},CONTRATOS,{"company.externalCode", "location.externalCode"},"Prueba",JoinKind.LeftOuter))

in

#"Custom"

OBS: #"Columnas reordenadas16" is the last step before my attempt to solve it 

then i get this Error Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Can you help me sort it out?

[Anonymous]

Sample Data, this is the query i am working on ( not showing every column bc i cant)

Clave de Plaza	Estatus	ID PUESTO	ID RS	JI	ID_CT	Centro de Trabajo	LOCALIDAD	ID_LOC
83466821	VACANTE	15000477	71	83456901	28	CERCOTEC 7 NORTE	TIJUANA	20040001
MP86473401	OCUPADA	80100472	71	86417411	703	PACIFICO ADMINISTRATIVO	TIJUANA	20040001
MP86470551	VACANTE	80100472	71	86417861	703	PACIFICO ADMINISTRATIVO	TIJUANA	20040001
MP86473221	VACANTE	80100472	71	86706891	703	PACIFICO ADMINISTRATIVO	TIJUANA	20040001
MP86475151	OCUPADA	80100472	71	86406471	703	PACIFICO ADMINISTRATIVO	TIJUANA	20040001
MP86471341	OCUPADA	80100472	71	86466441	703	PACIFICO ADMINISTRATIVO	TIJUANA	20040001

So depending on the Value of [ID RS] (39,41,46,48,55,56,58,70,71,74,75,83,88,91) i need it to merge with this table  using ID RS as key

ID  RS               CC 

39	CVN39010102
41	CVN41909001
46	CVN46010104
48	CVN48010101
55	BTL55909001
56	BTL56909001
58	CVN58010103
70	TVI70010101
71	CMS71909001
74	TVI74909007
75	CMS75010101
83	TTC83909001
88	TLC88909001
91	CCM91909001

and if its not any of those values i need it to merge with this table using [ID RS] and [ID_LOC] as keys

ID RS	ID LOC	CC
93	300030001	CCM93010101
93	290050001	CCM93010102
50	40030001	CCM93010103
93	300440001	CCM93010104
43	300470001	CCM93010105
30	300710001	CCM93010106
31	210850001	CCM93010107

[edhans · Accepted solution]

That is a bit different than what I originally understood. I think a conditional merge is overcomplicating it.

Just do two merges, then some if/then/else logic on nulls.

See the attached file. This is in Excel because it was easier to get your tables loaded from a spreadsheet vs PBIs "Enter Data" screen.\

The lower green table is what you want the result to be. See the queries in Power Query.

I had to change the last record in your first table since you gave me all 71's in the ID RS field, which was a 100% match to the first table. I changed it to 93 and 300030001 for the ID_LOC field.

[Anonymous]

thanks a lot! i didnt think of that way of doing it, if i change for example ID RS then the CC column will update right?

[edhans]

Yes. It should. If you look at the steps in the query, it is really doing two merges. The final two steps do the following:

• If the first merge fails, it returns a null, so it gets the 2nd match. Otherwise the 1st match.
• Removes all the merge columns.

Note: This does NOT compensate if there is no match for either one. The logic is it would pull null, because it would pull the results of the 2nd merge, which is also merge if nothing matches. You'd need an additional IF/THEN/ELSE wrapped around that to test for that and return "NOTHING FOUND" or whatever you want if that is a risk.

Glad you are moving forward! 👍

[edhans]

The following code will conditionally merge with table a or table b.

= if varSeason = "X" then Table.NestedJoin(#"Changed Type", {"Item Number"}, PartAItems, {"Part A Item"}, "ItemWeights", JoinKind.LeftOuter) else Table.NestedJoin(#"Changed Type", {"Item Number"}, PartBItems, {"Part B Item"}, "ItemWeights", JoinKind.LeftOuter)

My conditional tables are PartAItems and PartBItems. Notice I forced the merge column to be ItemWeights vs the normal table name so the expansion always works. Without working through each and every bit of syntax in your statement, it looks like you are incorrectly referring to fields as "field" vs {"field"} in a few places. so Table.NestedJoin isn't getting what it needs as a list in the right places in your first bit of code. The 2nd part looks more correct. So:

Table.NestedJoin(#"Columnas reordenadas16","ID RS",CCunicos,"ID RS", "Prueba", JoinKind.LeftOuter))

is the one that looks wrong.

You should probably just manually do the merges separately, then copy and paste the generated Table.NestedJoin statement. Typing all of the brackets, squigley brackets, quotes, commas, etc. manually will drive one to insanity.😀

Or copy and paste my code, then replace with your table and field names.

[Anonymous]

i tried that solution but it didnt work i still get the same error

[edhans]

Then please post sample data if possible, because it works here.

[artemus]

Your if statements needs to be in the context of a row. Each of your statements in [] refers to the column of the current row. Since you aren't adding a column or doing a filter you have no row context.

More technically, The error means that the variable "_" is not defined. "_" is automatically defined by using the "each" keyword which is shorthand for (_) =>. In other words, "each" is a lamda function which takes 1 input.