Forum Discussion
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.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.
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)
4 Replies
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
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)
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"
