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

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

    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'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