Forum Discussion
Help with M code + Vlookup
Hello!
I am creating a complex database that will have import info from Share point list and connect to Data Lake databases.
I have no experience/knowledge with M code but I would like to use it instead of DAX because its possible to filter the rows in the query mode that will improve a lot the speed/size of this power bi file since I will use a lot of large databases.
I will make also easier when I will use the same table mirror for this table for other filtering conditions for the same table.
Table A
| ECO | DICE MECO |
| 686638 | 596517 |
| 686638 | 596517 |
| 686638 | 612180 |
| 686638 | 621342 |
Table B
| FA Proj. ID | Proj. ref. type | Proj. ref. DICE MECO(s) / ECO(s) |
| 22 | DICE MECO | 596517 |
| 23 | DICE MECO | 643711 |
| 24 | DICE MECO | 660949 |
| 25 | DICE MECO | 685692 |
| 26 | DICE MECO | 695569 |
| 27 | DICE MECO | 704182 |
| 28 | ECO | 686638 |
| 30 | DICE MECO | 668426 |
| 31 | DICE MECO | 612180 |
| 31 | DICE MECO | 621342 |
| 33 | DICE MECO | 685694 |
I would like to add the column "FA Proj. ID" in the table A based on the values for column B with 2 variants, when is ECO in the Proj. ref. type should look in the 1st column in the table A or when its DICE MECO in the Proj. ref. type for the 2nd column for the table A when is a match with the values for the column DICE MECO(s) / ECO(s) from the table B.
I tried to search similar cases but I didn´t find it.
Hope that I can get help with that!
Best regards,
André Fortunato
Hi, Anonymous
Sorry for the late reply because of the weekend. You can try the custom column in PQ. Sample file is below.
Like this:
let t1=Table.SelectRows(#"TableB",(x)=>x[DICE_MECO ECO]=[ECO]), t2= Table.SelectRows(#"TableB",(x)=>x[DICE_MECO ECO]=[DICE MECO]) in if Table.RowCount(t1)>0 then List.Sum(t1[FA Proj. ID]) else if Table.RowCount(t2)>0 then List.Sum(t2[FA Proj. ID]) else nullBest Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi, Anonymous
In this case, do you want 22 or 28? Can you show me the desired result?
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello v-janeyg-msft !
Thanks a lot for your reply and support!
My apologies for that, I added wrong values in the ECO column. See below the desired result:
Best regards,
André Fortunato
Hi, Anonymous
I need a complete logic, If the two columns of a row in table A can match the id in table B, how to deal with it, is there such a situation? Please explain.
Best Regards
Janey Guo