cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Proulxrs
Frequent Visitor

Remove duplicate from next row base on another column in DAX

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.

DAX.png

Any help is appreciated 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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.

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
Frequent Visitor

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
Frequent Visitor

@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
Not applicable

@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.

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors