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.

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

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.

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.

  • 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é