Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
cgkas
Helper V
Helper V

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

Good day,

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

image.png

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

image.png

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 

image.png

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.

1 ACCEPTED SOLUTION
adudani
Super User
Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

View solution in original post

3 REPLIES 3
adudani
Super User
Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

@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. 

@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. 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors