Forum Discussion
SteveCampbell
9 years agoMemorable Member
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...
- 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.
ImkeF
9 years agoCommunity Champion
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.
SteveCampbell
9 years agoMemorable Member
You are awesome - that works perfectly! I've just started learnign DAX / Power Query so walking through your steps really helps me unsderstand the logic.
Thanks a lot!