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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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