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

[nbufff]

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! 

[shafiz_p · Accepted solution]

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

[nbufff]

That's really helpful!