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

[MohamedKamal]

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 

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.

[Vijay_A_Verma · Accepted solution]

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"

[MohamedKamal]

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 

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.

[MohamedKamal]

Solved 
Solved: Re: Need Help - Need query to filter the intersect... - Microsoft Power BI Community

[MohamedKamal]

Thanks a lot @Vijay_A_Verma You are really a Community Champion