VLookup in the same table

[Magnus-CPH-DK]

Hi guys,

I can't seem to figure out how to do a VLookup as one would do it in Excel for this particular problem.

I want to do the following:

"For each [Forudgående Kontrolnummer] that exists in [Kontrolnummer], return the first corresponding [Kontrolnummer_Type], else null"

The new column I am looking for should have the following values for the first 7 rows:

null

null

null

null (Since the value "4 " doesn't exist in [Kontrolnummer])

7A

7A

7A

In my search for an answer I stumbled upon Erik Svensens very interesting blog post about vlookup in Power Query. It almost solved my issue, however, I could only use it if I duplicated the table and referenced the duplicate, and once I closed Power Query, it just kept loading more and more data (even though my dataset only consists of about 10.000 rows).

I also tried merging the matching values from [Kontrolnummer] and [Forudgående_Kontrolnummer] in a new column, but it did not return the needed result.

Anyone who an help me out?

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @Magnus-CPH-DK ,

According to your description, here's my solution.

Add a custom column.

if Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Kontrolnummer]=[Forudgående Kontrolnummer]))=0 then null else Table.SelectRows(#"Changed Type",(x)=>x[Kontrolnummer]=[Forudgående Kontrolnummer]){0}[Kontrolnummer_Type]

Get the correct result.

Here's the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUTJ2BBJKsTrRSqZAhimcZwZkmMF55kCGOYhnAuZaAFkWLiAxMNcSyLJEcA0NgExDA6hALAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Kontrolnummer = _t, Kontrolnummer_Type = _t, #"Forudgående Kontrolnummer" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Kontrolnummer", Int64.Type}, {"Kontrolnummer_Type", type text}, {"Forudgående Kontrolnummer", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Kontrolnummer]=[Forudgående Kontrolnummer]))=0 then null else Table.SelectRows(#"Changed Type",(x)=>x[Kontrolnummer]=[Forudgående Kontrolnummer]){0}[Kontrolnummer_Type])
in
    #"Added Custom"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[artemus]

You will want to merge your table with itself using the two columns that need to match as the keys. After that expand the results (only select the type column) and do a group by on your joined column with any aggregate on the type column (lets say max). Finally, you will need to go in and change the aggregate from using List.Max to use List.First.