Forum Discussion

cinek15c's avatar
cinek15c
New Member
4 years ago
Solved

Sum of values from specific rows

Hi,   I've got a problem with creating a certain functionality. I've got some data that I imported to Power Query and it looks like this: Date Hour InboundCalls 03.01.2022   7 5 03.01...
Need Help
  • MarkLaf's avatar
    MarkLaf
    4 years ago

    You can pivot on the hours and perform row level operations you would like:

    Advanced Editor Code:

     

    let
    Source = #"Original Source",
    Types = 
        Table.TransformColumnTypes(Source,{{"InboundCalls", Int64.Type}}),
    Pivot = 
        Table.Pivot(Types, List.Distinct(Types[Hour]), "Hour", "InboundCalls", List.Sum),
    ApplyLogic = 
        Table.FromRecords( 
            List.Transform( 
                Table.ToRecords( Pivot ), 
                (row) => Record.TransformFields( row, {{"7", each 0},{"8", each row[7] + _}} ) 
            ), 
            Value.Type(Pivot) 
        ),
    Unpivot = 
        Table.UnpivotOtherColumns(ApplyLogic, {"Date"}, "Hour", "InboundCalls")
in
    Unpivot

     

    For explanation/convo on the technique used in ApplyLogic step, see https://stackoverflow.com/questions/31548135/power-query-transform-a-column-based-on-another-column

     

    (edit: added screenshot of steps in query editor)

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Since you plan to filter out Hour = 7, you can split the table into hours 7 & 8 and the rest of the data, group hours 7 & 8 together, then append the grouped rows with the rest of the data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdC5DQAhDETRVpBjhGyzHK4F0X8ba46MSSZ5wl9iDOKcWJKyKkUKofkWmvGB7muCxHylKSLhZXKffU8pI1ilbkh2yRqiU7J7sDwlQbBLjOSUKqJTqv5J8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Hour = _t, InboundCalls = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Hour", Int64.Type}, {"InboundCalls", Int64.Type}}),
    #"Filtered 7&8" = Table.SelectRows(#"Changed Type", each ([Hour] = 7 or [Hour] = 8)),
    #"Filtered Other" = Table.SelectRows(#"Changed Type", each not ([Hour] = 7 or [Hour] = 8)),
    #"Grouped 7&8" = Table.Group(#"Filtered 7&8", {"Date"}, {{"Hour", each List.Max([Hour]), type number}, {"InboundCalls", each List.Sum([InboundCalls]), type number}}),
    #"Appended Query" = Table.Combine({#"Grouped 7&8", #"Filtered Other"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}, {"Hour", Order.Ascending}})
in
    #"Sorted Rows"