Lookup ?

[J_o_n_a_s]

Hi. I created a simple example for my question. I would like to create column D.

I would like that if C matches A (from a different row or same row), then in column D it will give the result from column B.

A	B	C	D
Z-1	Dogs	Z-1	Dogs
Z-2	Cats	Z-2	Cats
Z-3	null	Z-1	Dogs

I have a lot of rows, so if possible I would like to do this in Power Query and i would not like to create a new table to merge with.

Thanks a lot

Jonas

[AlexisOlson · Accepted solution]

You can do a self-merge column C with column A and then expand column B to create column B:

Full sample query you can paste into the Advanced Editor of a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WitI1VNJRcslPLwZSIE6sDkjQCMhzTiyBCBpBBY2BPJiqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"C"}, #"Changed Type", {"A"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"B"}, {"D"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Changed Type",{{"A", Order.Ascending}})
in
    #"Sorted Rows"

[J_o_n_a_s]

Thanks Alexis. I should have known that 😀

Jonas