Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 us...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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 null

     

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

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 null

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi v-janeyg-msft ,

     

    No worries, I am in summer holidays from last Monday but I will be able to test the proposed solution only in the middle/end of the next week.


    Thanks a lot once again for your help!

     

    Best regards, André

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi v-janeyg-msft !

    I tested the code and works great.

    I just have a new issue that takes much more time to load the table A from a OBDC data source but I can load this table by a routine.

    Many thanks once again and have a great weekend,

    Regards, André