time difference between values

[luisat]

hey,

i log a machine state as soon it changes:

from this i would like to calculate how long the machine is in each state.

So the output should look like this:

Also it would be cool if I could show the machine state visually over time.

Thanks in advance.

Regards,

Luis

[luisat]

@FrankAT @Greg_Deckler

Thanks!

[FrankAT · Accepted solution]

Hi,

open Power Query Editor and following the steps:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDQMzDSMzIwMlAwNLYyMFDIz1OK1UEXB5K5iXmliTkYciZWxlj1mFoZGimkFhXlF2FImVkZGGLTAhQ3RbHGEtkaJKdZolmPQ48pstMske1BdRqKFC4tJginxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type date}, {"Column1.2", type time}, {"Column1.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1.1", "Date"}, {"Column1.2", "Time"}, {"Column1.3", "Status"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Date", Order.Ascending}, {"Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index1", {"Date"}, {{"Group", each _, type table [Date=date, Time=time, Status=text, Index=number, Index.1=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.NestedJoin([Group], {"Index"}, [Group], {"Index.1"}, "Table", JoinKind.LeftOuter)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Time", "Status", "Index", "Index.1", "Table"}, {"Date", "Time", "Status", "Index", "Index.1", "Table"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Expanded Custom", "Table", {"Time"}, {"Time.1"}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Expanded Table", "Subtraction", each [Time.1] - [Time], type duration),
    #"Grouped Rows1" = Table.Group(#"Inserted Time Subtraction", {"Date", "Status"}, {{"Count", each List.Sum([Subtraction]), type duration}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows1",{{"Date", type date}, {"Status", type text}})
in
    #"Changed Type"

REgards FrankAT

[Greg_Deckler]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...