Forum Discussion

nbufff's avatar
nbufff
Icon for Helper I rankHelper I
1 year ago
Solved

Help! How to use Powery Query to match value in the above rows

Dear community,

I encountered a problem when dealing with power query in Power BI.

It seemed simple in Excel

eg. B3=IF(MATCH(A3,$A$2:A2,0),"Sharing","").

      B4=IF(MATCH(A4,$A$2:A3,0),"Sharing","")

      B5=IF(MATCH(A5,$A$2:A4,0),"Sharing","")

Basically Column B is to check the value above, if found then return as "Sharing" or showing #N/A.

 

How can we make it done in Power Query by adding the custom column from Column A?

Appreciate the help! 

Need Help
Show and Tell
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi nbufff  You can acheive this using index column, List.Contains and List.FirstN function. First create a index column in power query. See image below:

     

    Now create a custom column to identify matching values above. Go to home tab and click advance editor. Create a custom column to identify. See image below:

     

    Here is the custom column, where #"Added Index" is the previous steps:

        Matching = Table.AddColumn(#"Added Index", "MatchingAbove", 
        each if List.Contains(List.FirstN(#"Added Index"[Material], [Index]-1), [Material]) then "Sharing" else "")

     

    You will get your desired result. See image below:

    Now you can remove the index column. That's it.

     



    Hope this helps!!
    If this solved your problem, please accept it as a solution!!

    Best Regards,
    Shahariar Hafiz

2 Replies

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    1 year ago

    Hi nbufff  You can acheive this using index column, List.Contains and List.FirstN function. First create a index column in power query. See image below:

     

    Now create a custom column to identify matching values above. Go to home tab and click advance editor. Create a custom column to identify. See image below:

     

    Here is the custom column, where #"Added Index" is the previous steps:

        Matching = Table.AddColumn(#"Added Index", "MatchingAbove", 
        each if List.Contains(List.FirstN(#"Added Index"[Material], [Index]-1), [Material]) then "Sharing" else "")

     

    You will get your desired result. See image below:

    Now you can remove the index column. That's it.

     



    Hope this helps!!
    If this solved your problem, please accept it as a solution!!

    Best Regards,
    Shahariar Hafiz