Power Query / Power Pivot - stock calculation

I have a table in Power query, which besides other fields has the following key fields: CMT | Year | Week | Customer | Transaction | Value AB587 | 2019 | 12 | Tom | Purchase | 200 AB587 | 2019 ...

Need Help

ImkeF

7 years ago

If you're worried about performance, you can use this trick to substantially improve speed for a case like this: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-powerquery-powerbi/

Your code would look like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQytTBX0lEyMjC0BFKGRkAiJD8XSAaUFiVnJBangiUNlGJ18CkOTswBKTQ0xarOGJuhZtjNNMYwk7DlJfnJ2SAhMxxKTbDZb0pILdR+EySFFkSEFCHV8KDCrhBHWBFSjAgsgvYjhRZ2tTiCi5BiRHgBVcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CMT = _t, Year = _t, Week = _t, Customer = _t, Transaction = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CMT", type text}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Customer", type text}, {"Transaction", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Transaction]), "Transaction", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "YearWeek", each [Year] * 100 + [Week], Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"CMT"}, {{"All", (Partition) => Table.AddColumn(Partition, "Custom", each List.Sum(
            Table.AddColumn( 
                Table.SelectRows( 
                    Partition, let  _earWeek = [YearWeek] in each [YearWeek] <= _earWeek 
                ),
                "var", each [Sale]- [Purchase]
            )[var]
        )) }}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Year", "Week", "Customer", "Purchase", "Sale", "Stock", "YearWeek", "Custom"}, {"Year", "Week", "Customer", "Purchase", "Sale", "Stock", "YearWeek", "Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded All", each [Stock], each [Stock] + [Custom],Replacer.ReplaceValue,{"Stock"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Stock"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Stock", Int64.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",each [Stock], each [Stock] - [Custom],Replacer.ReplaceValue,{"Stock"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Stock", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"CMT", "Year", "Week", "Customer", "Purchase", "Sale", "Stock"})
in
    #"Removed Other Columns"

You'll find some more performance tricks here: https://www.thebiccountant.com/speedperformance-aspects/

ImkeF
7 years ago
Hi nexami ,

if you want to learn how to integrate M code into your own solution, this video might help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-code-into-your-existing/m-p/179314

Mariusz

Community Champion

7 years ago

Hi nexami

Please see the below output based on the original data sample provided.

Please see the below M expression I used to Pivot the data on transaction column and later creatred a running total for Purchase - sales to be deducted of the stock value for every line.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQytTBX0lEyMjC0BFKGRkAiJD8XSAaUFiVnJBangiUNlGJ18CkOTswBKTQ0xarOGJuhZtjNNMYwk7DlJfnJ2SAhMxxKTbDZb0pILdR+E5DCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CMT = _t, Year = _t, Week = _t, Customer = _t, Transaction = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CMT", type text}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Customer", type text}, {"Transaction", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Transaction]), "Transaction", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "YearWeek", each [Year] * 100 + [Week], Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Sum(
            Table.AddColumn( 
                Table.SelectRows( 
                    #"Added Custom1", let _cmt = [CMT], _earWeek = [YearWeek] in each [CMT] = _cmt and [YearWeek] <= _earWeek 
                ),
                "var", each [Sale]- [Purchase]
            )[var]
        )),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom2", each [Stock], each [Stock] + [Custom],Replacer.ReplaceValue,{"Stock"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Stock"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Stock", Int64.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",each [Stock], each [Stock] - [Custom],Replacer.ReplaceValue,{"Stock"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Stock", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"CMT", "Year", "Week", "Customer", "Purchase", "Sale", "Stock"})
in
    #"Removed Other Columns"

Sales and purchase will be normal sum aggregation where Last Stock is as below.

Last Stock = 
VAR _maxMonth = MAX( test[Week] )
VAR _maxYear = MAX( test[Year] )
RETURN 
CALCULATE(
    SUM( test[Stock] ),
    test[Week] = _maxMonth && 
    test[Year] = _maxYear
)

Due to complicated M expression and the size of your data this will perform rather slow.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.