Forum Discussion
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
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.
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"
4 Replies
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
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
