Forum Discussion

SteveCampbell's avatar
SteveCampbell
Memorable Member
9 years ago
Solved

Calculating time between steps

Hi,   I have been stuck on this now for a little and cannot seem to figure it out.   I have the following table:   item no step a step b step c step d item 1 11/20/2016   11/29/201...
  • ImkeF's avatar
    9 years ago

    Check this out:

     

        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyixJzVUwVNJRMjLQNzTUNzIwNANyQHxLZH6sDlSpEUTW0BRVNUKBMVQATdgEu7ApkGdgiGSWkREyxwKrG0A8AyM0B6C4GK7UHLu1Ftj8CFIQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"item no" = _t, #"step a" = _t, #"step b" = _t, #"step c" = _t, #"step d" = _t]),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"item no"}, "Attribute", "Value"),
        #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
        #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type date}}),
        AddIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Added Custom" = Table.AddColumn(AddIndex, "Duration", each if [Index]>0 and [item no]=AddIndex[item no]{[Index]-1} then Number.From([Value])-Number.From(AddIndex[Value]{[Index]-1}) else "" ),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Value"}),
        #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Duration")
    in
        #"Pivoted Column"

     

    If you also need the empty item numbers, just let me know.