Replicating a simple match function in query

[zb134]

I need to compare two columns and return a 1 if the value in Col B exists in Col A and 0 otherwise.

This is ofcourse easily achieved in excel using the MATCH funciton but I can't figure out how to do it in PowerQuery (need it within transformation not using DAX).

Any help appreciated!

Example Result

Col A	Col B	Result
123	321	1
321	211	0
221	412	0

[bolfri · Accepted solution]

= Table.AddColumn(Source, "Custom", each if List.Contains(Table.Column(Source, "Col A"), [Col B]) then 1 else 0)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjYyBJKGSrE60VC2kSGINACLGIFFTAyNICKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, Result = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Contains(Table.Column(Source, "Col A"), [Col B]) then 1 else 0),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Col A", Int64.Type}, {"Col B", Int64.Type}, {"Result", Int64.Type}, {"Custom", Int64.Type}})
in
    #"Changed Type"

[bolfri · Accepted solution]

= Table.AddColumn(Source, "Custom", each if List.Contains(Table.Column(Source, "Col A"), [Col B]) then 1 else 0)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjYyBJKGSrE60VC2kSGINACLGIFFTAyNICKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, Result = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Contains(Table.Column(Source, "Col A"), [Col B]) then 1 else 0),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Col A", Int64.Type}, {"Col B", Int64.Type}, {"Result", Int64.Type}, {"Custom", Int64.Type}})
in
    #"Changed Type"

[zb134]

Thanks, this is really helpful. However, with the actual data set it runs really slow. Is there any way to speed it up? I read something about list.buffer but don't know how to implement it.

[bolfri · Accepted solution]

Go to Advanced Editor.

Code Before:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjYyBJKGSrE60VC2kSGINACLGIFFTAyNICKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, Result = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Contains(Table.Column(Source, "Col A"), [Col B]) then 1 else 0)
in
    #"Added Custom"

Change the #Added Custom step to:

= let
buffer_list = Table.Column(Source, "Col A"),
#"StepOne" = Table.AddColumn(Source, "Custom", each if List.Contains(buffer_list, [Col B]) then 1 else 0)
in #"StepOne"

Code After:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjYyBJKGSrE60VC2kSGINACLGIFFTAyNICKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, Result = _t]),
    #"Added Custom" = 
        let
            buffer_list = Table.Column(Source, "Col A"),
            #"StepOne" = Table.AddColumn(Source, "Custom", each if List.Contains(buffer_list, [Col B]) then 1 else 0)
        in #"StepOne"
in
    #"Added Custom"

List.Buffer will work if you want to create your own list with values.

Doc: https://learn.microsoft.com/en-us/powerquery-m/list-buffer

In this case we are using a variable buffer_list that holds all values from Col A and then we are reffering to that variable, without creating it for every row. It will work faster.

[zb134]

Thanks for helping me with this. It did speed up things considerably. Howerver, my final step is to filter the Results column with just 0 staying and I run in to the same problem of it taking forever to load with almost a gigabyte + for a simple excel file. Any ideas?

[YukiK]

Create a calculated column like the following:

Result = IF( Table[Col A] in VALUES(Table[Col B]), 1, 0 )

Please consider giving it a thumbs up and accept as solution if this helps!

[zb134]

Thanks for the suggestion, but I need to do it within transformations with M Query not DAX