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 

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi v-janeyg-msft !


    The filter ut will be always the table B, if column Proj. type ref. is a ECO they should vlookup the value from the DICE MECO(s) / ECO(s) in the column ECO from table A and add a new column with the FA. Proj. iD in the table A from the table B, the same logic if the value from the column Proj. type ref. From table B is a DICE MECO but should vlookup the values from the table A from the column DICE MECO instead.

    it will be always distinct values for ECO or MECO in the table A or B but I am only interested to retrieve in the table A the DICE MECO / ECOs from table B and tag the Proj ID in the table A.

     

    Please let me know if you need more input đŸ™‚

     

    Best regards,

     

    AndrĂ© Fortunato 

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

      Anonymous  I understand and I will reply to you later.