Can not lookup in self-table with M

[Anonymous]

Hello everyone,

I need to do a self-table look up with the following features:

Say it I have three columns like this (they are simplified, real data are more complex):

COLUMN1      COLUMN2     COLUMN3

A1                   a1                  null

A2                   a2                  null

B1                   b1                  A1

B2                   b2                  A1

B3                   b3                  A2

C1                   c1                  B3

C2                   c2                  B1

C3                   c3                  B1

C4                   c4                  B4

...and so on

I need to get a COLUMN4 which takes COLUMN3 values, seach for them in COLUMN1 and get COLUMN2 as the output. With DAX that is as simple as LOOKUPVALUE(COLUMN2,COLUMN1,COLUMN3), but I can not figure it out how to do it with M. The output should look like:

COLUMN1      COLUMN2     COLUMN3      COLUMN4

A1                   a1                  null                  null

A2                   a2                  null                  null

B1                   b1                  A1                    a1

B2                   b2                  A1                    a1 

B3                   b3                  A2                    a2

C1                   c1                  B3                    b3

C2                   c2                  B1                    b1

C3                   c3                  B1                    b1

C4                   c4                  B4                    b4

...and so on

It is highly important to do it in the same table, trying to avoid creating a new one from a "merge" or something like that, since I have to make further transformations.

Thank you very much for your help.

-J

[AlB · Accepted solution]

Hi @Anonymous 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYzBDcAgDAN3yZtPQxdoGAPxKFmMWZiMEsuqxCsnn+Na5bnmkCRzvABp6QuVof6hsdkB8RmC7a6HyBSAmN2icMoBUQzBKQcYpwqnPB/ipgDYPq0t", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),


    #"Added Custom" = Table.AddColumn(#"Changed Type", "Column4", each try if [Column3] = "" then null else List.Range(#"Changed Type"[Column2], List.PositionOf(#"Changed Type"[Column1], [Column3]), 1){0} otherwise "Not found", type text)
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

I do not get any errors. You're probably doing something differently. See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Sorry @AlB , I was replying to @watkinnc  in my last message. I adapted your solution and it worked! It will take me a few to understand it 100% but it could get me out of trouble. Thank you!

[AlB · Accepted solution]

Hi @Anonymous 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYzBDcAgDAN3yZtPQxdoGAPxKFmMWZiMEsuqxCsnn+Na5bnmkCRzvABp6QuVof6hsdkB8RmC7a6HyBSAmN2icMoBUQzBKQcYpwqnPB/ipgDYPq0t", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),


    #"Added Custom" = Table.AddColumn(#"Changed Type", "Column4", each try if [Column3] = "" then null else List.Range(#"Changed Type"[Column2], List.PositionOf(#"Changed Type"[Column1], [Column3]), 1){0} otherwise "Not found", type text)
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[watkinnc]

Table.AddColumn(NameOfPriorStep, "NewColumnName", each if List.Contains([Column3], [Column1]) then [Column1] else null)

--Nate

[Anonymous]

Hi there,

this solution is quite similar to:

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

but in both cases, the editor gives me the next output Error:

Expression.Error: We cannot convert the value "X" to type List.

Value = "X"

Type = [Type]

where "X" is each row value.