Sum of values from specific rows

[cinek15c]

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.2022	8	91
03.01.2022	9	172
03.01.2022	10	111
04.01.2022	7	3
04.01.2022	8	89
04.01.2022	9	197
04.01.2022	10	193
05.01.2022	7	1
05.01.2022	8	80
05.01.2022	9	196
05.01.2022	10	165

What I need to achieve is to create new column, where for each Date, the value of InboundCalls:
* for Hour = 7  ->  is 0 or NULL, or just the same value as before, doesn't matter I'll filter it out later 

* for Hour = 8  -> is sum of InboundCalls from Hour 8 and Hour 7 -> so for example NewInboundCalls for Hour 8 in date 03.01.2022 = 91 + 5 = 96

* for the rest Hours  -> is the same as before

I don't know the Power Query M formula language, so I need help with this task. I just need to implement this one specific funcionality. If you could help, I'll be grateful.

Greetings.

[cinek15c]

Wow, guys, thank you so much! You are awesome!
All of your posts were really helpful and solved my problem in different ways but with satisfied results.

I choose @MarkLaf  answer as a solution because of idea to use pivot table. This seems to me to be the simplest solution.

[AlexisOlson]

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"

[MarkLaf · Accepted solution]

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)

[tackytechtom]

Hi @cinek15c ,

Here is a possible solution:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

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]),
    #"Grouped Rows" = Table.Group(Source, {"Date"}, {{"Count", each _, type table [Date=nullable date, Hour=nullable number, InboundCalls=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddIndexColumn", each Table.AddIndexColumn([Count], "Index", 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"AddIndexColumn"}),
    #"Expanded AddIndexColumn" = Table.ExpandTableColumn(#"Removed Other Columns", "AddIndexColumn", {"Date", "Hour", "InboundCalls", "Index"}, {"Date", "Hour", "InboundCalls", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded AddIndexColumn", "Index_2", each [Index] + 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Date", "Index"}, #"Added Custom1", {"Date", "Index_2"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Date", "Hour", "InboundCalls", "Index", "Index_2"}, {"Changed Type.Date", "Changed Type.Hour", "Changed Type.InboundCalls", "Changed Type.Index", "Changed Type.Index_2"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Changed Type1", "Custom", each if [Hour] = 7 then [Changed Type.InboundCalls] else if [Hour] = 8 then [InboundCalls] +[Changed Type.InboundCalls]else [InboundCalls]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index", "Index_2", "Changed Type.Date", "Changed Type.Hour", "Changed Type.InboundCalls", "Changed Type.Index", "Changed Type.Index_2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Hour", Int64.Type}, {"InboundCalls", Int64.Type}, {"Custom", Int64.Type}})
in
    #"Changed Type"

Does this help you? 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom