Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sum Integer with hour

Hello, 

I need a help with sum a Integer with hour.

In this example I have this base (Hour;int) and i need a solution in power query to obtain (Results):

HourIntResults
15:00:002015:00:20
13:00:003013:00:30
10:00:0012010:02:00

 

Anybody can help me?

 

Thanks a lot.

  • HI Anonymous ,
    Please paste my M Code in advanced editor and let me know if there is any query:
    M Code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sgvLVLSUfLMKwGSQanFpTklxUqxOtFKhqZWBgZABBQ2AhEQPpAJljSGSxqDJcF8Y6ikAVzSEKIVyDMCCaDLmoJIdFEThCCSLRYGCGGEy4CiEOFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"(blank).2"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Hour", type time}, {"Int", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each #time(0,0,0) + #duration(0,0,0,[Int])),
    #"Inserted Time" = Table.AddColumn(#"Added Custom", "Time", each DateTime.Time([Custom]), type time),
    #"Inserted Hour" = Table.AddColumn(#"Inserted Time", "Hour.1", each Time.Hour([Time]), Int64.Type),
    #"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Time]), Int64.Type),
    #"Inserted Second" = Table.AddColumn(#"Inserted Minute", "Second", each Time.Second([Time]), type number),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Second",{"Custom", "Int", "Time"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns2", "Results", each [Hour] + #duration(0,[Hour.1],[Minute],[Second])),
    #"Changed Type2" = Table.SelectColumns(Table.TransformColumnTypes(#"Added Custom2",{{"Results", type time}}),{"Hour","Results"})
    in
    #"Changed Type2"


    Hope this will help you if this solves your problem then give it a kudos and accept it as a solution.

    Thank You.

2 Replies

  • KeyurPatel14's avatar
    KeyurPatel14
    Responsive Resident

    HI Anonymous ,
    Please paste my M Code in advanced editor and let me know if there is any query:
    M Code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sgvLVLSUfLMKwGSQanFpTklxUqxOtFKhqZWBgZABBQ2AhEQPpAJljSGSxqDJcF8Y6ikAVzSEKIVyDMCCaDLmoJIdFEThCCSLRYGCGGEy4CiEOFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"(blank).2"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Hour", type time}, {"Int", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each #time(0,0,0) + #duration(0,0,0,[Int])),
    #"Inserted Time" = Table.AddColumn(#"Added Custom", "Time", each DateTime.Time([Custom]), type time),
    #"Inserted Hour" = Table.AddColumn(#"Inserted Time", "Hour.1", each Time.Hour([Time]), Int64.Type),
    #"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Time]), Int64.Type),
    #"Inserted Second" = Table.AddColumn(#"Inserted Minute", "Second", each Time.Second([Time]), type number),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Second",{"Custom", "Int", "Time"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns2", "Results", each [Hour] + #duration(0,[Hour.1],[Minute],[Second])),
    #"Changed Type2" = Table.SelectColumns(Table.TransformColumnTypes(#"Added Custom2",{{"Results", type time}}),{"Hour","Results"})
    in
    #"Changed Type2"


    Hope this will help you if this solves your problem then give it a kudos and accept it as a solution.

    Thank You.

  • KeyurPatel14's avatar
    KeyurPatel14
    Responsive Resident

    Hi Anonymous ,
    Please check the post again I have updated the M Code.
    And please if this helps then mark it as a solution.
    Thank You.