Join 2 tables with ID of 1 table that must be CONTAINED (not equal) in the ID of the other table ?

[Anonymous]

Hello,

I have a table A with column IDA1 (it is of type text) and VALA like this :

IDA1                                                                                               VALA

tredsj:43294545396645096,gfrgre:56657443676654                 whatever1

null                                                                                             whatever2

5434556657445457                                                                    whatever3

sdfjlfjregjgjgkm:63745734764767657                                        whatever4

and a Table B with column IDB1 (it is of type Int64) and IDB2 (it is of type Int64) and VALB like this :

IDB1                                                         IDB2                                          VALB

643693737696369346                             3465556433465636                   rewhatever1

43294545396645096                               5432656564567742                   rewhatever2

63745734764767657                               63364563474354                       rewhatever3

Here is the source code :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYyxDgIxDEP/JXMHuDiJ2l+pOiBdWlRg6Z3g96lODIfswbKenTPtw9etJ/ASIRCOqpBL1NDqaMOTqIoBrDYDKNDnftv97eNKJWQ6FctRCBjyG80/OwF8ANta+7P24a1PPV5J2SbHMJ02/ZuASvkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDA1 = _t, VALA = _t]),
    A = Table.TransformColumnTypes(Source,{{"IDA1", type text}, {"VALA", type text}}),
    SourceB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYs7CgMxDESvElRvkXg+wmcxLlIYUi8huX7kdAtCDG/mjREm3JFId+9ExxH1JVW1Q+FC5/q+nu/1Wecj5jGCaJ2i0G3q3vdGBWtfopzJdtHaXzOSSjBdl1beamRgK0UJ8WIh5vwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDB1 = _t, IDB2 = _t, VALB = _t]),
    B = Table.TransformColumnTypes(SourceB,{{"IDB1", Int64.Type}, {"IDB2", Int64.Type}, {"VALB", type text}})
in
    B

I need to get all the lines where IDB1 or IDB2 in table B is contained in IDA1 of table A and I have no clue how to do this join in Power Query, could you enlighten me please ?

Thanks

[ImkeF]

Hi @Anonymous 
yes, it's been a while and this is quite a complex topic that would require more efford from me than I am currently able to invest.
So if no one else jumps into this thread, I'd recommend you open a new one for it.

[ImkeF]

Hi @Anonymous ,
not sure which table should be filtered, so I included both options:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "VYyxDgIxDEP/JXMHuDiJ2l+pOiBdWlRg6Z3g96lODIfswbKenTPtw9etJ/ASIRCOqpBL1NDqaMOTqIoBrDYDKNDnftv97eNKJWQ6FctRCBjyG80/OwF8ANta+7P24a1PPV5J2SbHMJ02/ZuASvkC", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [IDA1 = _t, VALA = _t]
    ), 
    A = Table.TransformColumnTypes(Source, {{"IDA1", type text}, {"VALA", type text}}), 
    SourceB = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "VYs7CgMxDESvElRvkXg+wmcxLlIYUi8huX7kdAtCDG/mjREm3JFId+9ExxH1JVW1Q+FC5/q+nu/1Wecj5jGCaJ2i0G3q3vdGBWtfopzJdtHaXzOSSjBdl1beamRgK0UJ8WIh5vwB", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [IDB1 = _t, IDB2 = _t, VALB = _t]
    ), 
    B = Table.TransformColumnTypes(SourceB, {{"IDB1", Int64.Type}, {"IDB2", Int64.Type}, {"VALB", type text}}), 
    FilterA = Table.AddColumn(
        A, 
        "ContainsB", 
        each List.AnyTrue(List.Transform(B[IDB1] & B[IDB2], (l) => Text.Contains([IDA1], Text.From(l))))
    ), 
    FilterB = Table.AddColumn(
        B, 
        "ContainsA", 
        each Table.SelectRows(A, (A) => Text.Contains(A[IDA1], Text.From([IDB1])) or Text.Contains(A[IDA1], Text.From([IDB2])))
    ), 
    #"Expanded ContainsA" = Table.ExpandTableColumn(FilterB, "ContainsA", {"VALA"}, {"VALA"})
in
    #"Expanded ContainsA"

However, for larger tables this will be an expensive operation. To speed it up, I would recommend buffers that I have included in the file attached.

[Anonymous]

Hello @ImkeF

I finally had time to use your answer. It seems very close to what I need.

FilterA is a nice filter but it doesn't JOIN the 2 tables so I've left that out.

Regarding FilterB, it works but not for cases where my ID fields are null (In the sample I gave, IDA1 can have null values but IDB1 and IDB2 can actually have null values too). 

When calling the step "Expanded ContainsA", it stops when there is a line with Error (I actually don't understand why ExpandTableColumn() has this behaviour instead of giving at least all the lines where there is no Error by the way, do you know ?).

As a not so nice workaround I tried modifying the step FilterB like this (I know "ABCDEFGHIJKLMNOPQRSTUVWXYZ" is never present in neither IDB1 nor IDB2, so if my ID is null then I use this value) :

= Table.AddColumn(
        B, 
        "ContainsA", 
        each Table.SelectRows(A, (A) => Text.Contains(A[IDA1], if Text.From(IDB1) = null then "ABCDEFGHIJKLMNOPQRSTUVWXYZ" else Text.From(IDB1)) or Text.Contains(A[IDA1], if Text.From([IDB2]) = null then "ABCDEFGHIJKLMNOPQRSTUVWXYZ" else Text.From([IIDB2])))
    )

I don't have an error anymore while expanding but I end up with wrong data. On the lines where there was Error before, now my column ContainsA contains a non-empty list, whereas it should be empty. Do you know why there is a non-empty list and more importantly, do you know how to adjust your answer to handle null values ?

When the value of an ID column is null, this column shouldn't return any match.