Query Editor equivalent to Excel MACTH function

[huguest]

Hello,

Trying to do somthing that I am sure is simple but cannot figure it out...  I havea  table with an ID and a Parent ID column.  I need to add a custom column that will tell me if, for each ID (row), there is another row where the Parent ID matches that ID.

In Excel, I would use something like "=MATCH(B2,A:A,0)", but cannot figure out how to do in in the Power Bi Query Editor.

I know how to do it with a Merge / Expand, but think there should be something simpler...

Thanks!

[edhans]

I don't know if I would say it is easier, but it can be done without a merge. I'm returning a 1 or 0, but you could wrap that in an if/then/else to return Yes/No or whatever.

The code to do it is below.

The key is this statement:

    #"Added Matches" = Table.AddColumn(#"Changed Type", "Matches", each let varID = [ID]
                        in 
                          Table.RowCount(
                            Table.SelectRows(#"Changed Type", each [Parent ID] = varID)
                          )
                        )

1. It assigns the current row ID to varID
2. It then does a filtering of the table (previous #"Changed Type" table) where the [Parent ID] = varID
3. It counts the rows. It will return 0 if there are none, or the row count, 1+ if there are 1 or more matches.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcixDQAgCATAXb6mEQTjLIT91/D97nLdWDAkxhpOHSmoK23KXUwyQqy/mHk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Parent ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Parent ID", Int64.Type}}),
    #"Added Matches" = Table.AddColumn(#"Changed Type", "Matches", each let varID = [ID]
                        in 
                          Table.RowCount(
                            Table.SelectRows(#"Changed Type", each [Parent ID] = varID)
                          )
                        )
in
    #"Added Matches"

[Greg_Deckler]

Maybe @ImkeF or @edhans can help with a Power Query function to do it. In DAX you would likely use LOOKUPVALUE or something like MAXX(FILTER(...)...)