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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MohamedKamal
Frequent Visitor

Need Help - Need query to filter the intersect records from two tables.

Hi,
I have two tables as the screenshot, the 1st table is for active devices per week, and 2nd one is list of all the device, what I need is:

get a list of what are the serials that were not active in each week. for example 

 

MohamedKamal_0-1657644325341.png

the result in week 21 will be active devices by serial are (1111,3333) and not active device are (2222,4444,5555,6666,7777).

I have a drop-down list for the weeks and want to create a table visual, once I change the week the table shows the not active devices.

Thanks in advance.

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

CODE FOR Weekly active device table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5BDoAgEAP/smdM2IL6GML/v2EbkOjqXCaZS9uawTdkwJI5oWA9PXMh0si4806kkA8ihXwSKeQ56SOX/8mVK5FCfj+p3yf9Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, #"Serial number" = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", type text}, {"Serial number", Int64.Type}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Week"}, {{"Active devices", each _[Serial number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Not active devices", each List.Difference(#"All device"[Serial number],[Active devices])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Active devices", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Not active devices", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values1"

CODE FOR All device table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcexEQAgCATBXj4mURSLYei/DW9+s+3WgkJLE60Nsp0ESeeAHOeCXKdAynkgTzMf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial number" = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial number", Int64.Type}, {"ID", Int64.Type}})
in
    #"Changed Type"

 

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

CODE FOR Weekly active device table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5BDoAgEAP/smdM2IL6GML/v2EbkOjqXCaZS9uawTdkwJI5oWA9PXMh0si4806kkA8ihXwSKeQ56SOX/8mVK5FCfj+p3yf9Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, #"Serial number" = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", type text}, {"Serial number", Int64.Type}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Week"}, {{"Active devices", each _[Serial number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Not active devices", each List.Difference(#"All device"[Serial number],[Active devices])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Active devices", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Not active devices", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values1"

CODE FOR All device table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcexEQAgCATBXj4mURSLYei/DW9+s+3WgkJLE60Nsp0ESeeAHOeCXKdAynkgTzMf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial number" = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial number", Int64.Type}, {"ID", Int64.Type}})
in
    #"Changed Type"

 

Hi,
I have to tables as the screenshot, the 1st table is for active devices per week, and 2nd one is list of all the device, what I need is:

get a list of what are the serials that was not active in each week. for example 

 

MohamedKamal_0-1657644325341.png

the result in week 21 will be active devices by serial are (1111,3333) and not active device are (2222,4444,5555,6666,7777).

I have a drop-down list for the weeks and want to create a table visual, once I change the week the table shows the not active devices.

Thanks in advance.

Thanks a lot @Vijay_A_Verma You are really a Community Champion

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors