Forum Discussion
Compare 2 rows from the same table
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.
1 Reply
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"
