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
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.
Solved! Go to 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"
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
61 | |
53 | |
27 | |
16 | |
10 |