Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSample 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthanks 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?
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:
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! 👍
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingi tried that solution but it didnt work i still get the same error
Then please post sample data if possible, because it works here.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYour 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.