Forum Discussion
Conditional Merge
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?
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.
8 Replies
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.
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.Then please post sample data if possible, because it works here.
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 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.
