Forum Discussion
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.
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
Community Champion
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.
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! 👍