Forum Discussion
Solved
Sub aggregation between records
Hi, With the following table Timestamp Wood Fuel 2017-02-16 01:00 300 2017-02-16 02:00 30 2017-02-16 03:00 40 2017-02-16 06:00 50 2017-02-16 12:00 180 ...
An "Artist Impression" in this video (explaining the code below) how it could look like in Power Query with the data from an Excel file.
let Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Subaggregation between values.xlsx"), null, true), Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"Timestamp", type datetime}, {"Wood", Int64.Type}, {"Fuel", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "StartIndex", each if [Wood] = null then [Index] else null, Int64.Type), #"Filled Up" = Table.FillUp(#"Added Custom",{"StartIndex"}), #"Added Custom1" = Table.AddColumn(#"Filled Up", "EndIndex+1", each if [Wood] <> null then [Index] else null), #"Filled Up1" = Table.FillUp(#"Added Custom1",{"EndIndex+1"}), #"Added Index1" = Table.AddIndexColumn(#"Filled Up1", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"NewColumn",JoinKind.LeftOuter), #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index", "EndIndex+1", "Index.1"}), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Removed Columns", "NewColumn", {"EndIndex+1"}, {"EndIndex+1"}), #"Inserted Subtraction" = Table.AddColumn(#"Expanded NewColumn", "Count", each [#"EndIndex+1"] - [StartIndex], type number), #"Added Custom2" = Table.AddColumn(#"Inserted Subtraction", "TotalFuel", each if [Wood] <> null and [StartIndex] <> null then List.Sum(List.Range(#"Removed Columns"[Fuel],[StartIndex],[Count])) else null), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"StartIndex", "EndIndex+1", "Count"}) in #"Removed Columns1"
ImkeF Just because something can be done in DAX doesn't mean it should be. I'd say my approach (if correct) wouldn't be memory efficient and If it was something I was working on I would definitely push the transform upstream to Power Query or further if poss.
An "Artist Impression" in this video (explaining the code below) how it could look like in Power Query with the data from an Excel file.
let
Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Subaggregation between values.xlsx"), null, true),
Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"Timestamp", type datetime}, {"Wood", Int64.Type}, {"Fuel", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "StartIndex", each if [Wood] = null then [Index] else null, Int64.Type),
#"Filled Up" = Table.FillUp(#"Added Custom",{"StartIndex"}),
#"Added Custom1" = Table.AddColumn(#"Filled Up", "EndIndex+1", each if [Wood] <> null then [Index] else null),
#"Filled Up1" = Table.FillUp(#"Added Custom1",{"EndIndex+1"}),
#"Added Index1" = Table.AddIndexColumn(#"Filled Up1", "Index.1", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"NewColumn",JoinKind.LeftOuter),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index", "EndIndex+1", "Index.1"}),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Removed Columns", "NewColumn", {"EndIndex+1"}, {"EndIndex+1"}),
#"Inserted Subtraction" = Table.AddColumn(#"Expanded NewColumn", "Count", each [#"EndIndex+1"] - [StartIndex], type number),
#"Added Custom2" = Table.AddColumn(#"Inserted Subtraction", "TotalFuel", each if [Wood] <> null and [StartIndex] <> null then List.Sum(List.Range(#"Removed Columns"[Fuel],[StartIndex],[Count])) else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"StartIndex", "EndIndex+1", "Count"})
in
#"Removed Columns1"