Forum Discussion
cinek15c
4 years agoNew Member
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...
- 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 UnpivotFor 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)
cinek15c
4 years agoNew Member
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.