Forum Discussion

RichArt's avatar
RichArt
Frequent Visitor
2 years ago
Solved

Find days between different change types

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 ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

  • jgeddes's avatar
    jgeddes
    2 years ago

    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"

     

jgeddes's avatar
jgeddes
Super User
2 years ago

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's avatar
RichArt
Frequent Visitor
2 years ago

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's avatar
    jgeddes
    Super User
    2 years ago

    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's avatar
      RichArt
      Frequent Visitor
      2 years ago

      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

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        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