Forum Discussion
Help with Time difference between multiple row per day and month
- 6 years ago
I'm not sure what other analyses you'll need to do with your data, but for the one you asked about, I would pivot your data so you get Enter and Exit on the same row, so you can use Duration.TotalHours() to calculate the duration. Please put this M code into a blank query to see how to do it with your data. Note that I did OCR to extract your data from the pic but it didn't split all the columns so I had to do extra steps in the beginning to address that (that you won't need to do with your data).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddK7bgMhEAXQX7G2tuR5MI+lWyku3KRI0ln+/98IYMW7DjMV6CIdXQbu94WAAFCLFnFczgsjqHrboF4AL+PYavEKcNqjdt7Dtlw/f762j215nA+WElJsIVUokzXStn5vt/+UoSXUCTm0+GmFxdxVk2JaZb7kSMNiBlbkjbJXMWgQD8sOVg+TXkYOmGBtNoUmbKRxMVaHzOK/l7T3iaVPaSLCmeaVy6z1NG5mWtajRXD4YoKd2qPxxQSzXqupxVYbDa2TNdKwloOhJxSHFD+pqJcLcHLHPi+iWevpXuzxCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CHAVE = _t, MAT = _t, HORA = _t, #"4 KORA" = _t, #"4 FL_CTR_ENTRADA_SAIDA" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CHAVE", Int64.Type}, {"MAT", Int64.Type}, {"HORA", type text}, {"4 KORA", type time}, {"4 FL_CTR_ENTRADA_SAIDA", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "HORA", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"HORA.1", "HORA.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"HORA.1", type text}, {"HORA.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"HORA.2", "Date"}, {"4 KORA", "Time"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "ForPivot", each Number.RoundUp([Index]/2), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CHAVE", "HORA.1", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"4 FL_CTR_ENTRADA_SAIDA"]), "4 FL_CTR_ENTRADA_SAIDA", "Time"),
#"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Duration Hours", each Duration.TotalHours([SAIDA]-[ENTRADA])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration Hours", type number}})
in
#"Changed Type2"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
I'm not sure what other analyses you'll need to do with your data, but for the one you asked about, I would pivot your data so you get Enter and Exit on the same row, so you can use Duration.TotalHours() to calculate the duration. Please put this M code into a blank query to see how to do it with your data. Note that I did OCR to extract your data from the pic but it didn't split all the columns so I had to do extra steps in the beginning to address that (that you won't need to do with your data).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddK7bgMhEAXQX7G2tuR5MI+lWyku3KRI0ln+/98IYMW7DjMV6CIdXQbu94WAAFCLFnFczgsjqHrboF4AL+PYavEKcNqjdt7Dtlw/f762j215nA+WElJsIVUokzXStn5vt/+UoSXUCTm0+GmFxdxVk2JaZb7kSMNiBlbkjbJXMWgQD8sOVg+TXkYOmGBtNoUmbKRxMVaHzOK/l7T3iaVPaSLCmeaVy6z1NG5mWtajRXD4YoKd2qPxxQSzXqupxVYbDa2TNdKwloOhJxSHFD+pqJcLcHLHPi+iWevpXuzxCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CHAVE = _t, MAT = _t, HORA = _t, #"4 KORA" = _t, #"4 FL_CTR_ENTRADA_SAIDA" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CHAVE", Int64.Type}, {"MAT", Int64.Type}, {"HORA", type text}, {"4 KORA", type time}, {"4 FL_CTR_ENTRADA_SAIDA", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "HORA", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"HORA.1", "HORA.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"HORA.1", type text}, {"HORA.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"HORA.2", "Date"}, {"4 KORA", "Time"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "ForPivot", each Number.RoundUp([Index]/2), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CHAVE", "HORA.1", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"4 FL_CTR_ENTRADA_SAIDA"]), "4 FL_CTR_ENTRADA_SAIDA", "Time"),
#"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Duration Hours", each Duration.TotalHours([SAIDA]-[ENTRADA])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration Hours", type number}})
in
#"Changed Type2"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat