Find days between different change types

[RichArt]

Hi,

This is my first post on this forum and I am just learning Power Query, now i have an issue that is really put my mind out of it.

For a project change type i need to find the number of days between an Error change and the latest date before that Error

If there is no Error change within the same project i do not need this project in the outcome.

To be sure: The dates are in European style (e.g.  Day-month-year)  I do not mind if you change it to another style 😉

Available data

Project ID	Change type	Date
P01	Basis	01/01/2024
P01	Error	04/01/2024
P02	Basis	01/03/2024
P02	Agreement	02/03/2024
P02	Error	03/03/2024
P02	Error	06/03/2024
P02	Agreement	05/03/2024
P03	Basis	01/04/2024
P03	Agreement	02/04/2024
P04	Basis	01/05/2024
P04	Error	03/05/2024

The result table should look like (third column is not needed in table but I added it for more clarity):

Project ID	days between error and earlier date in project	(explanation, not needed in actual table)
P01	3	(difference between 04-01-2024 and 01-01-2024)
P02	1	(difference between 03-03-2024 and 02-03-2024)
P02	3	(difference between 06-03-2024 and 03-03-2024)
P04	2	(difference between 03-05-2024 and 01-05-2024)

I dearly hope somebody can help me.

Regards

RichArt

[dufoq3 · Accepted solution]

Hi @RichArt,

Solution 1 (if you don't want date sort)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwVNJRckosziwG0gaG+kBkZGBkohSrA5N0LSrKLwJJmqBJGqHpNEaXdEwvSk3NTc0rASkwwqIAbrQxPkkzQkaboikwRnOYCbokusNQFJig6TZFl0R2NUwyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Change type" = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Project ID"}, {{"All", each Table.Sort(_, {{"Date", Order.Ascending}}) , type table}}),
    Ad_ErrorPositions = Table.AddColumn(GroupedRows, "ErrorPositions", each List.PositionOf([All][Change type], "Error", Occurrence.All), type list),
    Ad_Days = Table.AddColumn(Ad_ErrorPositions, "Days", each
     [ a = { Duration.TotalDays([All][Date]{[ErrorPositions]{0}} - [All][Date]{[ErrorPositions]{0}-1}) },
       b = if List.Count([ErrorPositions]) > 1 then a & List.Transform(List.Skip(List.Zip({ [ErrorPositions], {null} & List.RemoveLastN([ErrorPositions], 1) })), (x)=> Duration.TotalDays([All][Date]{x{0}} - [All][Date]{x{1}}))
           else if List.Count([ErrorPositions]) = 1 then a else null
     ][b], type list),
    FiteredRows = Table.SelectRows(Ad_Days, each [Days] <> null),
    RemovedColumns = Table.RemoveColumns(FiteredRows,{"All", "ErrorPositions"}),
    ExpandedDays = Table.ExpandListColumn(RemovedColumns, "Days")
in
    ExpandedDays

Solution 2 (with date sort)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwVNJRckosziwG0gaG+kBkZGBkohSrA5N0LSrKLwJJmqBJGqHpNEaXdEwvSk3NTc0rASkwwqIAbrQxPkkzQkaboikwRnOYCbokusNQFJig6TZFl0R2NUwyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Change type" = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Project ID"}, {{"All", each Table.Sort(_, {{"Date", Order.Ascending}}), type table}}),
    Ad_ErrorPositions = Table.AddColumn(GroupedRows, "ErrorPositions", each List.PositionOf([All][Change type], "Error", Occurrence.All), type list),
    Ad_Days = Table.AddColumn(Ad_ErrorPositions, "Days", each
       List.Transform([ErrorPositions], (x)=> Duration.TotalDays([All][Date]{x} - [All][Date]{x-1}))
       
  , type list),
    FiteredRows = Table.SelectRows(Ad_Days, each List.Count([Days]) > 0),
    RemovedColumns = Table.RemoveColumns(FiteredRows,{"All", "ErrorPositions"}),
    ExpandedDays = Table.ExpandListColumn(RemovedColumns, "Days")
in
    ExpandedDays

[RichArt]

Hi @dufoq3  and @jgeddes 

Thank you both so much for these fast reply's and solutions.  I am really gratefull there are still people who are willingly and able to help others, thanks!

I set up multiple variations to double check and the options from the both of you are a solution and work great.

Actually i will stick to the solution of @jgeddes  since I am able to keep the other columns as well, probarly the solution from @dufoq3 would be able to do so but i cannot find how at this moment.  I did not implement it on my actual data set but will be doing this tonight, i will keep you posted.

Thank again!

[dufoq3]

You're welcome, just kind advice: for future think in advance what your output should looks like and be careful with sample data - there have to be no mistakes.

[RichArt]

Yes i noted that already as a learning from this topic 👍

[Anonymous]

Hi @RichArt ,

Please try this way:
First, duplicate the original table:

Put all of this M functions into the Advanced Editor in the duplicated table which will add an Index column for all rows where [Change type] = "Error":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwVNJRckosziwG0ob6hvpGBkYmSrE6MCnXoqL8IrCUCbKUEZIuY1RdICnH9KLU1NzUvBKwtBG6NMxQY31j3FJm+A01RZY2RnKOCapzjNF0mqA6xwRJpymqThMk55jCXRoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Change type" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", type text}, {"Change type", type text}, {"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Change type] = "Error")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Project ID"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Change type", "Date", "Index"}, {"Change type", "Date", "Index"})
in
    #"Expanded Count"

Then create a Blank Query:

Put all of these M functions into the Advanced Editor in the Blank Query:

let
    Source = Table.NestedJoin(Table, {"Project ID", "Change type", "Date"}, #"Table (2)", {"Project ID", "Change type", "Date"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "Custom Date", each 
        let 
            currentRow = _,
            projectId = currentRow[Project ID],
            changeType = currentRow[Change type],
            index = currentRow[Index],
            date = currentRow[Date]
        in 
        if changeType = "Error" then 
            if index = 1 then 
                let 
                    sameProjectRows = Table.SelectRows(#"Expanded Table (2)", each [Project ID] = projectId),
                    filteredRows = Table.SelectRows(sameProjectRows, each [Change type] <> "Error" and [Date] < date),
                    maxDate = if 
                    Table.IsEmpty(filteredRows) then null else List.Max(Table.Column(filteredRows, "Date"))
                in 
                    maxDate 
            else 
                let 
                    sameProjectPreviousIndexRow = Table.SelectRows(#"Expanded Table (2)", each [Project ID] = projectId and [Change type] = "Error" and [Index] = index - 1),
                    previousDate = if Table.IsEmpty(sameProjectPreviousIndexRow) then null else sameProjectPreviousIndexRow{0}[Date]
                in 
                    previousDate
            else 
                null 
        ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Date] - [Custom Date]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type number}})
in
    #"Changed Type"

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RichArt]

Thank you for your help!

Your end result looks good but somehow the expanding in the balnk query does not align the index numbers with me. I tried different kind of merges but i cannot get it done.  This is the result with copy paste your solution:

[jgeddes]

There are likely a few ways to do this. Here is an example of one way...
start

end

code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwVNJRckosziwG0gaG+kBkZGBkohSrA5N0LSrKLwJJmqBJGqHpNEaXdEwvSk3NTc0rASkwwqIAbrQxPkkzQkaboikwRnOYCbokusNQFJig6TZFl0R2NUwyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Change type" = _t, Date = _t]),
    Custom2 = Table.TransformColumns(Source, {{"Date", each Date.From(_, "de-DE"), type date}}),
    #"Changed Type" = Table.TransformColumnTypes(Custom2,{{"Project ID", type text}, {"Change type", type text}, {"Date", type date}}),
    Custom1 = Table.AddColumn(#"Changed Type", "lastNonErrorDate", each List.Max(Table.SelectRows(#"Changed Type", (x)=> x[Project ID] = [Project ID] and x[Change type] <> "Error" and x[Date] < [Date])[Date]), type date),
    #"Added Custom" = Table.AddColumn(Custom1, "Days", each if [Change type] = "Error" then Number.From([Date]) - Number.From([lastNonErrorDate]) else null, Int64.Type)
in
    #"Added Custom"

[RichArt]

Oh Wauw,  thats quick.  thanks

Looks like yout hit it but unfortunatly in your END table  row 6 , I need the days between the first error and the second error  date entry, so this row should end up with  3 days  (difference between row 5 and row 6)

[jgeddes]

To clarify, if there is one error in a project then you require the days between the error date and the last non-error date. If there is more than one error in the project you require the days between the error dates regardless if there are non-error dates inbetween?

[RichArt]

No it is a bit different.

We have row entry's, this is filling the data, in order of the date the entry had done, so the first row of the project is the earliest date, the seconde entry the first date after that.

The first entry is always the "Basis" in [changed type].  After that we can have a undifined amount of changes called "agreement"  or " error".   Now i need the days between the "error" and the last entry before  that error, no matter what that [change type] is (within the same project number).     Short: if an error is created i need the difference between that entry date and the entry date before the error entry.

Sorry, it is  difficult to explain 

[jgeddes]

That helps to clarify. 
Final question. Is the entry order independent of the date?
In your example for P02 there is an agreement change that occurs with a date of 05/03/2024 which is the 5th entry for P02 but the forth entry for P02 is an error on 06/03/2024. So in order to calculate the days you are looking for order of entry and not date order?

[RichArt]

Oooh  thats a good catch, sorry for that.

It should be the date order,  in my actually data set i sorted first the projects and second the date from earliest to latest

[jgeddes · Accepted solution]

Here is my corrected code. @dufoq3  solution 2 will run quicker than this on larger datasets.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwVNJRckosziwG0gaG+kBkZGBkohSrA5N0LSrKLwJJmqBJGqHpNEaXdEwvSk3NTc0rASkwwqIAbrQxPkkzQkaboikwRnOYCbokusNQFJig6TZFl0R2NUwyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Change type" = _t, Date = _t]),
    Custom2 = Table.TransformColumns(Source, {{"Date", each Date.From(_, "de-DE"), type date}}),
    #"Changed Type" = Table.TransformColumnTypes(Custom2,{{"Project ID", type text}, {"Change type", type text}, {"Date", type date}}),
    Custom1 = Table.AddColumn(#"Changed Type", "lastNonErrorDate", each List.Max(Table.SelectRows(#"Changed Type", (x)=> x[Project ID] = [Project ID] /*and x[Change type] <> "Error"*/ and x[Date] < [Date])[Date]), type date),
    #"Added Custom" = Table.AddColumn(Custom1, "Days", each if [Change type] = "Error" then Number.From([Date]) - Number.From([lastNonErrorDate]) else null, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Days] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Project ID", "Days"})
in
    #"Removed Other Columns"

[dufoq3 · Accepted solution]

Hi @RichArt,

Solution 1 (if you don't want date sort)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwVNJRckosziwG0gaG+kBkZGBkohSrA5N0LSrKLwJJmqBJGqHpNEaXdEwvSk3NTc0rASkwwqIAbrQxPkkzQkaboikwRnOYCbokusNQFJig6TZFl0R2NUwyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Change type" = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Project ID"}, {{"All", each Table.Sort(_, {{"Date", Order.Ascending}}) , type table}}),
    Ad_ErrorPositions = Table.AddColumn(GroupedRows, "ErrorPositions", each List.PositionOf([All][Change type], "Error", Occurrence.All), type list),
    Ad_Days = Table.AddColumn(Ad_ErrorPositions, "Days", each
     [ a = { Duration.TotalDays([All][Date]{[ErrorPositions]{0}} - [All][Date]{[ErrorPositions]{0}-1}) },
       b = if List.Count([ErrorPositions]) > 1 then a & List.Transform(List.Skip(List.Zip({ [ErrorPositions], {null} & List.RemoveLastN([ErrorPositions], 1) })), (x)=> Duration.TotalDays([All][Date]{x{0}} - [All][Date]{x{1}}))
           else if List.Count([ErrorPositions]) = 1 then a else null
     ][b], type list),
    FiteredRows = Table.SelectRows(Ad_Days, each [Days] <> null),
    RemovedColumns = Table.RemoveColumns(FiteredRows,{"All", "ErrorPositions"}),
    ExpandedDays = Table.ExpandListColumn(RemovedColumns, "Days")
in
    ExpandedDays

Solution 2 (with date sort)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwVNJRckosziwG0gaG+kBkZGBkohSrA5N0LSrKLwJJmqBJGqHpNEaXdEwvSk3NTc0rASkwwqIAbrQxPkkzQkaboikwRnOYCbokusNQFJig6TZFl0R2NUwyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Change type" = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Project ID"}, {{"All", each Table.Sort(_, {{"Date", Order.Ascending}}), type table}}),
    Ad_ErrorPositions = Table.AddColumn(GroupedRows, "ErrorPositions", each List.PositionOf([All][Change type], "Error", Occurrence.All), type list),
    Ad_Days = Table.AddColumn(Ad_ErrorPositions, "Days", each
       List.Transform([ErrorPositions], (x)=> Duration.TotalDays([All][Date]{x} - [All][Date]{x-1}))
       
  , type list),
    FiteredRows = Table.SelectRows(Ad_Days, each List.Count([Days]) > 0),
    RemovedColumns = Table.RemoveColumns(FiteredRows,{"All", "ErrorPositions"}),
    ExpandedDays = Table.ExpandListColumn(RemovedColumns, "Days")
in
    ExpandedDays