cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Leoaq Frequent Visitor
Frequent Visitor

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?

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
edhans Super Contributor
Super Contributor

Re: Conditional Merge

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.

View solution in original post

8 REPLIES 8
artemus Member
Member

Re: Conditional Merge

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.

edhans Super Contributor
Super Contributor

Re: Conditional Merge

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.

Leoaq Frequent Visitor
Frequent Visitor

Re: Conditional Merge

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

edhans Super Contributor
Super Contributor

Re: Conditional Merge

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

Leoaq Frequent Visitor
Frequent Visitor

Re: Conditional Merge

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

Clave de PlazaEstatusID PUESTOID RSJIID_CTCentro de TrabajoLOCALIDADID_LOC
83466821VACANTE15000477718345690128CERCOTEC 7 NORTE TIJUANA20040001
MP86473401OCUPADA801004727186417411703PACIFICO ADMINISTRATIVOTIJUANA20040001
MP86470551VACANTE801004727186417861703PACIFICO ADMINISTRATIVOTIJUANA20040001
MP86473221VACANTE801004727186706891703PACIFICO ADMINISTRATIVOTIJUANA20040001
MP86475151OCUPADA801004727186406471703PACIFICO ADMINISTRATIVOTIJUANA20040001
MP86471341OCUPADA801004727186466441703PACIFICO ADMINISTRATIVOTIJUANA20040001

 

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 

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

 

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 RSID LOCCC 
93300030001CCM93010101
93290050001CCM93010102
5040030001CCM93010103
93300440001CCM93010104
43300470001CCM93010105
30300710001CCM93010106
31210850001CCM93010107

 

 

edhans Super Contributor
Super Contributor

Re: Conditional Merge

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.

View solution in original post

Leoaq Frequent Visitor
Frequent Visitor

Re: Conditional Merge

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 Super Contributor
Super Contributor

Re: Conditional Merge

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! 👍

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 266 members 3,044 guests
Please welcome our newest community members: