Remove duplicate from next row base on another column in DAX

[Proulxrs]

Hello,

been trying some formula but not able to get a hold of the right one. I'm able to create multiple request in power query to get the data I want, but it slows the process too much.

I've got a equipment lot and want to have the time diffence beetwen each change of status. I'm able to do so, problem is that some equipment don't change status necesserly. I would like to maybe add another column where I would see if this row is needed or not then filter it.

Example:

Equipment B here would only send back 1 row and equipment C would return 2 rows.

Any help is appreciated 🙂

[Anonymous · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc+7DcAwCEXRXahdEAeI5S6/KSzvv0as+ClCCh06zeW1RjslYqtrrszjXKinRgdQPJ5A83gBy0R+8Z4o7BEhyR4RkigkUUiKR4SUPSKk0SKNQmr+JYT0v2gb8mF/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equipment = _t, Datetime = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Equipment", type text}, {"Datetime", type time}, {"Status", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Equipment", Order.Ascending}, {"Datetime", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Equipment", "Datetime", "Status"}),
    // First, you add a column that tells you whether the entry is the very first
    // entry for the current equipment.
    #"Added IsFirstEntry" = Table.AddColumn(
        #"Reordered Columns", "IsFirstEntry",
        (outer) => 
            outer[Index] = 1
            or
            Table.SelectRows(
                #"Reordered Columns",
                (inner) => inner[Index] = outer[Index] - 1
            )[Equipment]{0} <> outer[Equipment]
    ),
    // Then, you add a column that tells you whether there's a change
    // in status on the current line.
    #"Added IsChange" = Table.AddColumn(
        #"Added IsFirstEntry", "IsChange",
        (outer) =>
            outer[IsFirstEntry]
            or
            Table.SelectRows(
                #"Added IsFirstEntry",
                (inner) => inner[Index] = outer[Index] - 1
            )[Status]{0} <> outer[Status]
    ),
    // Then, you add a column that tells you if the row is the first for the
    // current equipment OR if the row holds a different status than the row
    // before it.
    #"Added ShouldRetain" = Table.AddColumn(#"Added IsChange", "ShoudRetain", each [IsFirstEntry] or [IsChange]),
    // Last thing, you just filter for the rows where the above condition is TRUE.
    #"Filtered Rows" = Table.SelectRows(#"Added ShouldRetain", each [ShoudRetain]),
    // You can remove the supporting columns.
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"IsFirstEntry", "IsChange", "ShoudRetain"})
in
    #"Removed Columns"

Take a good look at the code and see what it does to an example set of data. Just past this into the Advanced Editor in Power Query and.... watch in slow motion.

[Proulxrs]

Thanks @Anonymous ,

I've paste your solution and went through the transaction that where generated ans got how you were able to sort it out, thank you very much

[Proulxrs]

Hello, here is a link to a sample of the value I'm working with:

https://drive.google.com/drive/folders/12VQf44e8qucJm2wnjb7y8SlN0RHlJyZz?usp=sharing

As I told I was able to treat to value as I wanted in Power Query, but to do so I made a request for each individual equipment and it was very long to refresh the values... It's to calculate MTBF/MTTR,

Thanks!

[Proulxrs]

@Anonymous 

Thanks, I'll send a exemple in text format later on, was able to do it with Power Query, but not exactly with the efficienty as it would with DAX, I guess you'll have a lighter solution then mine,

Thanks!

[Anonymous]

@Proulxrs 

This is a job for Power Query, not DAX. I could give you the M code for PQ... but since I can't easily copy the table as it's not in a text format, I'll wait for you to post a version with data I can easily copy into my PQ. We don't generally like typing in data that could be copied. Waste of time.