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