Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
Need your assistance for the below query.
I have one table that is fetched in PowerBI as below
ID | MessageId | Eventtype | Timestamp | SystemTypeId |
1 | 101 | Pack | 14/02/18 00:00 | ABC |
2 | 102 | Unpack | 14/02/18 00:00 | ABC |
3 | 103 | Unpack | 14/02/18 01:00 | ABC |
4 | 104 | Pack | 14/02/18 01:00 | ABC |
5 | 105 | Unpack | 14/02/18 01:00 | ABC |
6 | 101 | Pack | 14/02/18 00:00 | XYZ |
7 | 102 | Unpack | 14/02/18 00:00 | XYZ |
8 | 104 | Pack | 14/02/18 01:00 | XYZ |
9 | 105 | Pack | 14/02/18 01:00 | XYZ |
I want to compare the rows with the same MessageID.
For example: I am comparing MessageID 101, then all the values like Eventtype, Timestamp are matching for both the systems i.e. ABC and XYZ.
But for the MessageID 105 the value for Eventtype is not matching or both the systems i.e. ABC and XYZ hence this message will not be counted as a MATCH message.
Finally i want to show it on the dashboard as
14/02/18 00:00 | 14/02/18 01:00 |
Count of Messages Count of Messages
ABC XYZ ABC XYZ
2 2 3 1
I require a flag across the above table that will help me to obtain the above result in PowerBI.
Can you please assist me with the same.
Thank you so much in advance.
You can use Grouping and Pivoting in the Query editor
Here is an advanced query script using your sample data. Having the values on the same row means you can also Calculated the difference and filter for only non zero differences.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0AJEBicnZII6JvoGRvqGFgoGBlYEBUMDRyVkpVidayQisEkSG5hUQUGsMVmuMXa0hqloTsFoTbC5AU2kKVmlKlKlmBP0VERkFVmlOhL9gai0IuhWm0hLuVrwqYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, MessageId = _t, Eventtype = _t, Timestamp = _t, SystemTypeId = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"MessageId", Int64.Type}, {"Eventtype", type text}, {"Timestamp", type datetime}, {"SystemTypeId", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"SystemTypeId", "Timestamp"}, {{"Count", each Table.RowCount(_), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[SystemTypeId]), "SystemTypeId", "Count", List.Sum),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Diff", each [ABC]-[XYZ]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Diff", Int64.Type}})
in
#"Changed Type1"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |