Look up value from other row of same table (like VLOOKUP in Excel)

[Andy4BI]

Hello, 

I have the following table "People"

ID	Nickname	Full Name	ParentNickname	ParentID ??
1	Andy	Andreas Müller
2	Berta	Beate Schneider	Fred	? (expectet: 6)
3	Clara	Claudi Wagner	Fred	? (expectet: 6)
4	Dora	Daurene Dauderer	Harry	? (expectet: 8 )
5	Elmir	Elmar Otterbach	Andy	? (expectet: 1)
6	Fred	Friedrich Holland
7	Georg	Hans-Georg Meyer	Elmir	? (expectet: 5)
8	Harry	Harald Zwibler	Dora	? (expectet: 4)

Now I'd like to calculate with M Query the column ParentID. So I'm looking for a function like "VLOOKUP" in Excel or in DAX:

ParentID = LOOKUPVALUE( People[ID], People[Nickname], People[ParentNickname])

It would be great, if some could help me. 

Thank you very much in advance

Andy

[Greg_Deckler · Accepted solution]

@Andy4BI Perhaps try Table.MatchesAnyRows.

[AlexisOlson · Accepted solution]

Merge the query with itself and expand the ID column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZA9C8IwEIb/ypFZB79d1apdioODYOlwbY42EFO4tkj/m5t/zPRCC07PveHh7iVpqhZqpg5O9wFM2EDy/VhL7F9AZbNULf10JG5RiC3BvagcGS3OhUmLtvLhZJExsNMGHli6f2ntQ1SLE2HH5Ag8/SbRYmTuxdv4dLYvw4HIcGtb4hyLaiw8aNtx9wBDmk1RQVxbi05P/Xd+ulLNpVxwzVwCJNQTT2cGcT9VEKLV8HybPPyFtM6yHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Nickname = _t, #"Full Name" = _t, ParentNickname = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Nickname", type text}, {"Full Name", type text}, {"ParentNickname", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ParentNickname"}, #"Changed Type", {"Nickname"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"ID"}, {"ParentID"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Changed Type",{{"ID", Order.Ascending}})
in
    #"Sorted Rows"

[Andy4BI]

Great! Thank you for the quick solution. 

[Greg_Deckler · Accepted solution]

@Andy4BI Perhaps try Table.MatchesAnyRows.