Forum Discussion
Create Hour from Text
Hi, what I am trying to do is to create a new column (measure) that will calculate the differences between pick begin AND pick end. The result should be in minutes. The format is like below: ...
Anonymous
This is not my measure. You can also try by defining a new column as per the calculated column code that I've providedDEFINE COLUMN 'Pick Order'[Column Result] = VAR PickBegin = VALUE ( RIGHT ( 'Pick Order'[PICK Time pick begin], 2 ) ) + 60 * VALUE ( LEFT ( 'Pick Order'[PICK Time pick begin], 2 ) ) VAR PickEnd = VALUE ( RIGHT ( 'Pick Order'[PICK Time pick end], 2 ) ) + 60 * VALUE ( LEFT ( 'Pick Order'[PICK Time pick end], 2 ) ) RETURN PickEnd - PickBegin EVALUATE SUMMARIZECOLUMNS ( 'Pick Order'[PICK Time pick begin], 'Pick Order'[PICK Time pick end], 'Pick Order'[Column Result] )
It's supposed to use PQ to conduct such a data shaping task.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jc3BDcAwCAPAXXj3gTFJ2lmi7L9GY3iZkxHsbUAOewyB186zLQC/DsQo4/OlXiH7qr6izIBMsvdnGZPZ5h2u09H7oP45su8FOfVPcc4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"pick begin" = _t, #"pick end" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"pick begin", Int64.Type}, {"pick end", Int64.Type}}),
TotalMinutes = Table.AddColumn(#"Changed Type", "Total Minutes",
each let
begin=#time(Number.IntegerDivide([pick begin],100), Number.Mod([pick begin],100),0),
end = #time(Number.IntegerDivide([pick end],100), Number.Mod([pick end],100),0)
in Duration.TotalMinutes(end-begin)
)
in
TotalMinutes
Hi, thank you. I am not able to use PQ