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"

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

    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: