Compare 2 rows from the same table

[Anonymous]

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.

[stretcharm]

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"