Forum Discussion
Convert transactions to balance
Yes, I'd say the measure is the way to go here. It should work as desired in the chart if you take the date-field from your date-table and not from the transactions-table.
Link to file: https://www.dropbox.com/s/dc8budcqd68ogz4/PBI_CumTotalAllDates.pbix?dl=0
Community ChampionAn advanced Power Query solution below, creating a record for each possible combination SKU/Warehouse/Date with quantities and balances.
It is assumed that your data has unique SKU/Warehouse/Date combinations (i.e. max 1 transaction per SKU / Warehouse / Date), otherwise a grouping step must be added.
If your data set is large, you should expect quite some runtime, but I already managed to reduce runtime by adding helper columns PrevSKU and PrevWarehouse with the values from the previous rows: Power Query is much faster when values on 1 row can be compared with each other, rather than values on different rows,
let
Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
Typed1 = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Warehouse", type text}, {"SKU", Int64.Type}, {"Qty", Int64.Type}}),
UniqueSKUs = Table.Distinct(Typed1, {"SKU"}),
RemovedOthers = Table.SelectColumns(UniqueSKUs,{"SKU"}),
WarehouseLists = Table.AddColumn(RemovedOthers, "Warehouse", each List.Distinct(Table.Column(Typed1,"Warehouse"))),
ExpandedWarehouse = Table.ExpandListColumn(WarehouseLists, "Warehouse"),
DateLists = Table.AddColumn(ExpandedWarehouse, "Date", each List.Distinct(Table.Column(Typed1,"Date"))),
ExpandedDate = Table.ExpandListColumn(DateLists, "Date"),
Merged = Table.NestedJoin(ExpandedDate,{"SKU", "Warehouse", "Date"},Typed1,{"SKU", "Warehouse", "Date"},"NewColumn",JoinKind.LeftOuter),
ExpandedQty = Table.ExpandTableColumn(Merged, "NewColumn", {"Qty"}, {"Qty"}),
ReplacedNullsWithZero = Table.ReplaceValue(ExpandedQty,null,0,Replacer.ReplaceValue,{"Qty"}),
SortedSKUWHSDate = Table.Sort(ReplacedNullsWithZero,{"SKU", "Warehouse", "Date"}),
SKUWarehouse = Table.SelectColumns(SortedSKUWHSDate,{"SKU", "Warehouse"}),
PrevSKUWarehouse = Table.InsertRows(SKUWarehouse,0,{[SKU = null,Warehouse = null]}),
AddedPrevSKUWarehouse = Table.FromColumns({Table.ToRecords(SortedSKUWHSDate),Table.ToRecords(PrevSKUWarehouse)}),
Expanded1 = Table.ExpandRecordColumn(AddedPrevSKUWarehouse, "Column1", {"SKU", "Warehouse", "Date", "Qty"}, {"SKU", "Warehouse", "Date", "Qty"}),
Expanded2 = Table.ExpandRecordColumn(Expanded1, "Column2", {"SKU", "Warehouse"}, {"Column2.SKU", "Column2.Warehouse"}),
RenamedColumns1 = Table.RenameColumns(Expanded2,{{"Column2.SKU", "PrevSKU"}, {"Column2.Warehouse", "PrevWarehouse"}}),
RemovedBottomRow = Table.Buffer(Table.RemoveLastN(RenamedColumns1,1)),
Balance = List.Generate(
() => [Counter = 0, Balance = RemovedBottomRow[Qty]{0}],
each [Counter] < Table.RowCount(RemovedBottomRow),
each [Counter = [Counter] + 1, Balance = if RemovedBottomRow[SKU]{Counter} = RemovedBottomRow[PrevSKU]{Counter} and
RemovedBottomRow[Warehouse]{Counter} = RemovedBottomRow[PrevWarehouse]{Counter}
then [Balance] + RemovedBottomRow[Qty]{Counter}
else RemovedBottomRow[Qty]{Counter}],
each [Balance]),
AddedBalance = Table.FromColumns({Table.ToRecords(RemovedBottomRow),Balance}),
Expanded3 = Table.ExpandRecordColumn(AddedBalance, "Column1", {"SKU", "Warehouse", "Date", "Qty", "PrevSKU", "PrevWarehouse"}, {"SKU", "Warehouse", "Date", "Qty", "PrevSKU", "PrevWarehouse"}),
RenamedColumns2 = Table.RenameColumns(Expanded3,{{"Column2", "Balance"}}),
RemovedColumns = Table.RemoveColumns(RenamedColumns2,{"PrevSKU", "PrevWarehouse"}),
Typed2 = Table.TransformColumnTypes(RemovedColumns,{{"Warehouse", type text}, {"Date", type date}, {"Balance", Int64.Type}})
in
Typed2
Community ChampionHi MarcelBeug,
that's an interesting approach. If you still have the test-environment with the large datasets, I'd be interested how the performance compares to this function:
(SourceTable as table, GroupColumns as list) =>
let
Source = SourceTable,
ChgType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
Partitions = Table.Buffer(Table.Group(ChgType, GroupColumns , {{"Partition", each _}})),
AddAllDates = Table.AddColumn(Partitions, "Dates", each List.Transform({Number.From(List.Min(ChgType[Date]))..Number.From(List.Max(ChgType[Date]))}, each Date.From(_))),
ExpandDates = Table.ExpandListColumn(AddAllDates, "Dates"),
AddRT = Table.AddColumn(ExpandDates, "RunningTotal", (Earlier) => List.Sum(Table.SelectRows(Earlier[Partition], each [Date]<=Earlier[Dates])[Qty])),
AddDaily = Table.AddColumn(AddRT,"Daily", (Earlier) => List.Sum(Table.SelectRows(Earlier[Partition], each [Date]=Earlier[Dates])[Qty])),
Cleanup = Table.RemoveColumns(AddDaily,{"Partition"})
in
Cleanup- tringuyenminh92
Memorable Member
Hi ImkeF,
Oh, Both of your solutions and MarcelBeug'solution are interesting to learn. I'm having concern about performance benchmark for your apporach and Marcel's approach, cause i usually think as your nested sum approach for balance.
- ImkeF
Hi tringuyenminh92,
yes, performance is an issue if you do it in M/the query-editor, therefore the suggestion for a measure.
But the Partition-step in my query helps if for whatever reason you want to do it in M ;-)
- tringuyenminh92
Hi ImkeF,
The measure approach is clear to me. Could i have your sample pbix file that you are doing the converting with above M script, so I could quickly observe step by step to understand the role of Partition step? :smileyvery-happy:
- MarcelBeug
Hi ImkeF
Your approach is much faster indeed. I'll take a closer look this evening. On first sight the result doesn't inlcude all combinations SKU/Warehouse/Date as indicated by OP in the original question.
Otherwise I wonder if that would be really necessary.
Expect another update from me later today.
Thanks,
Marcel
- ImkeF
Hi tringuyenminh92 and MarcelBeug,
I just remembered a trick I learned from Bill Szysz, that puts the performance here into a different dimension:
let
Source = TransactionTable,
ChgType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
Sort = Table.Buffer(Table.Sort(ChgType,{{"SKU", Order.Ascending}, {"Warehouse", Order.Ascending}, {"Date", Order.Ascending}})),
Partitions = Table.Buffer(Table.Group(Sort, {"Warehouse", "SKU"}, {{"all", (Earlier) => Table.AddColumn(Table.AddIndexColumn(Earlier, "Index",1,1), "RT", each List.Sum(List.Range(Earlier[Qty],0,[Index])))}})),
#"Expanded all" = Table.ExpandTableColumn(Partitions, "all", {"Date", "Qty", "RT"}, {"Date", "Qty", "RT"})
in
#"Expanded all"In the Partitions: Instead of filtering the table, one creates a list-range. I have absolutely no idea why this is so much faster, but it is :-)
Pls find the workbook here: https://www.dropbox.com/s/02850h7yrqe1kz5/PBI_RunningTotal2_3.xlsx?dl=0
You can adjust the size of the sample data by increasing the numbers in the number in the orange table:
