Forum Discussion

Tiolan's avatar
Tiolan
Frequent Visitor
9 years ago
Solved

Convert transactions to balance

Hello, Comunity.   Help me please :)  I have a transaction table and I can't find the way to calculate stock for certain date.   Example data:       In result, it should be certain qua...
Need Help
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

Tiolan and ImkeF

 

Although Imke's previous post was marked as the solution (and I fully agree), just because I would provide more information this evening: below my results of blowing up the transaction table for each combination SKU/WHS/Date applied to Imke's Transactions query in her PBIX-file.

The net result of blowing up the table is that you always have all warehouses and all SKU's in your slicers.

Compared to a previous post I improved performance by creating separate lists for the unique values and by using "combine tables" instead of "merge tables" (and then remove duplicates).

 

So just for the record (and educational purpose :catwink: ):

let
    Source            = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdDBDcAgCAXQXThLI2C0m/Rg3H+NQtSTYOL38vLB2DtkevRwpgYJPtLL8sJIauwZ1olHkTUtKJohTxQXKUDRFGcjXzZuQ3GGbuSgaAtr0DND7zWyPscbWpbp0PED", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Warehouse = _t, SKU = _t, Qty = _t]),
    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"}),
    AddedZeroQty      = Table.AddColumn(RemovedOthers, "Qty", each 0, Int64.Type),
    UniqueWHSs        = List.Buffer(List.Distinct(Table.Column(Typed1,"Warehouse"))),
    WarehouseLists    = Table.AddColumn(AddedZeroQty, "Warehouse", each UniqueWHSs, type {text}),
    ExpandedWarehouse = Table.Buffer(Table.ExpandListColumn(WarehouseLists, "Warehouse")),
    UniqueDates       = List.Buffer(List.Distinct(Table.Column(Typed1,"Date"))),
    DateLists         = Table.AddColumn(ExpandedWarehouse, "Date", each UniqueDates, type {date}),
    ExpandedDate      = Table.Buffer(Table.ExpandListColumn(DateLists, "Date")),
    CombinedTables    = Table.Combine({Typed1, ExpandedDate}),
    RemovedDuplicates = Table.Distinct(CombinedTables, {"Date", "Warehouse", "SKU"})
in
    RemovedDuplicates
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
9 years ago

Hi MarcelBeug,

yes, this is very nice code. Much faster than my attempts with Joins. So my M-winner of the day is this combined code :

 

let
    Source            = TransactionTable,
    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"}),
    AddedZeroQty      = Table.AddColumn(RemovedOthers, "Qty", each 0, Int64.Type),
    UniqueWHSs        = List.Buffer(List.Distinct(Table.Column(Typed1,"Warehouse"))),
    WarehouseLists    = Table.AddColumn(AddedZeroQty, "Warehouse", each UniqueWHSs, type {text}),
    ExpandedWarehouse = Table.Buffer(Table.ExpandListColumn(WarehouseLists, "Warehouse")),
    UniqueDates       = List.Buffer(List.Distinct(Table.Column(Typed1,"Date"))),
    DateLists         = Table.AddColumn(ExpandedWarehouse, "Date", each UniqueDates, type {date}),
    ExpandedDate      = Table.Buffer(Table.ExpandListColumn(DateLists, "Date")),
    CombinedTables    = Table.Combine({Typed1, ExpandedDate}),
    RemovedDuplicates = Table.Distinct(CombinedTables, {"Date", "Warehouse", "SKU"}),
    ChgType           = Table.TransformColumnTypes(RemovedDuplicates ,{{"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])))}})),
    ExpandAll         = Table.ExpandTableColumn(Partitions, "all", {"Date", "Qty", "RT"}, {"Date", "Qty", "RT"})
in    
    ExpandAll

 Better together :-)