How to search numbers in one table from other table with conditions?

[cgkas]

Good day,

I have this 2 input tables for which I want to make a SLQ like searches and obtain 2 output tables.

Output 1 would search: numbers in column "NUM" of TABLE1 that exists in TABLE2 where TP = "HO" and ST = "A", showing in output columns NUM, OC, TC, TP, ST

Output 2 would show: numbers in column "NUM" of TABLE1 that exists in TABLE2 where TP = "PD" and ST = "A", showing columns NUM, SG,DI, TP, ST 

How can be done this?

These are the tables in M code

let
    TBL1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/BDcQgDAR74c3DaxsMtUTpv40zJrlYEK2EMtrVcF1lTgJQamGPPEG5qyOIMR9IAmGwwZ5fHI1aNJC13uhtrZCnBxpj1RLqnhZImY2QBjWaC4n7mSWNfQ8kEM7y/W9oHdry4IcABp8aFqipD0oapE+D/Ojx5K0xyb88uC3v+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NUM = _t, OC = _t, TC = _t, SG = _t, DI = _t]),
    TBL2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY07DoAwDEPvkrlDnaYNjEgMTMBe9f7XoAkfVQyOLD/ZqZVAgTCxQrtJXefaz2IptVA90lxyZI8CbcfDk3OLZyRlM/r29y52bhVh1oh//97PtgRArRvH/+XjkkTBPjlwodYu", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Z1 = _t, NUM = _t, Z2 = _t, TP = _t, ST = _t, Z3 = _t])
in
    TBL2

Thanks for any help.

[adudani · Accepted solution]

hi @cgkas ,

appreciate you providing inputs and outputs as well as the PQ for input.

create 2 blank queries and paste the below codes into it

output1

let
    TBL1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/BDcQgDAR74c3DaxsMtUTpv40zJrlYEK2EMtrVcF1lTgJQamGPPEG5qyOIMR9IAmGwwZ5fHI1aNJC13uhtrZCnBxpj1RLqnhZImY2QBjWaC4n7mSWNfQ8kEM7y/W9oHdry4IcABp8aFqipD0oapE+D/Ojx5K0xyb88uC3v+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NUM = _t, OC = _t, TC = _t, SG = _t, DI = _t]),
    #"Removed Other Columns" = Table.SelectColumns(TBL1,{"NUM", "OC", "TC"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns", {"NUM"}, Table2, {"NUM"}, "Table2", JoinKind.Inner),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"TP", "ST"}, {"TP", "ST"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([TP] = "HO" and [ST]="A"))
in
    #"Filtered Rows"

output2 

let
    TBL1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/BDcQgDAR74c3DaxsMtUTpv40zJrlYEK2EMtrVcF1lTgJQamGPPEG5qyOIMR9IAmGwwZ5fHI1aNJC13uhtrZCnBxpj1RLqnhZImY2QBjWaC4n7mSWNfQ8kEM7y/W9oHdry4IcABp8aFqipD0oapE+D/Ojx5K0xyb88uC3v+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NUM = _t, OC = _t, TC = _t, SG = _t, DI = _t]),
    #"Removed Other Columns" = Table.SelectColumns(TBL1,{"NUM", "SG", "DI"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns", {"NUM"}, Table2, {"NUM"}, "Table2", JoinKind.Inner),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"TP", "ST"}, {"TP", "ST"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([TP] = "PD" and [ST] = "A"))
in
    #"Filtered Rows"

let me know if this resolves the question.

Appreciate a thumbs up if this is helpful.

[cgkas]

@adudani Thanks so much for kind help. It works pretty nice. I was wondering if performance won't be an issue with actual data since TBL1 is from a CSV file with more than 6 millions lines and TBL 2 has about 0.5 million lines. Outputs could be of about a few thousands of lines. 

[adudani]

@cgkas 

So the outputs are duplicate queries with merges, honestly not the most efficient solution but shouldn't take more than a few minutes to load tbh (5/7mins worst case I think).

However, if this doesn't meet the requirement / it's very slow let me know.