Forum Discussion

Jarrodg's avatar
Jarrodg
New Member
2 years ago
Solved

M Language code to calculate prior period difference for each entity

Hi,   I want to calculate the change in the Close field value from the prior period for each value in the Entity field. I managed to do this without the Entity field but I cannot figure out how to ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Jarrodg 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdE9DsMgDIbhuzBHjm3M31xF2XoBlPtfowgXqfrowvIIAy+9BznlVFYLR7ivsTDVHJ6jBwVQanFC3CDbBAOoVNKEBCCUfFQGSGR1QgEwyjqhArQFbRvFfrjwdl8/Q/Dp41pfwbePe3mUny3v+zXWSCaYy4WJDXu5FBLBYC6ZtGGxdU4qmMylUtuarWkcMdqaVgtWcxGKsmVzan+6rU2sWzin8Q2j3PMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Entity = _t, Close = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Entity", type text}, {"Close", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Entity"}, {{"Data", each Table.AddIndexColumn(_,"Index",0,1), type table }}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Close", "Index"}, {"Date", "Close", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Entity", "Date", "Index", "Close"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Movement", each if [Index]=0 then null else [Close]-List.Max(Table.SelectRows(#"Expanded Data",(x)=>x[Entity]=[Entity] and x[Index]=[Index]-1)[Close]))
in
    #"Added Custom"

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Jarrodg, I know that you've asked for this solution more than 2 months ago, but try also this if you have big dataset - this one should be much faster.

 

Result

 

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdE9DsMgDIbhuzBHjm3M31xF2XoBlPtfowgXqfrowvIIAy+9BznlVFYLR7ivsTDVHJ6jBwVQanFC3CDbBAOoVNKEBCCUfFQGSGR1QgEwyjqhArQFbRvFfrjwdl8/Q/Dp41pfwbePe3mUny3v+zXWSCaYy4WJDXu5FBLBYC6ZtGGxdU4qmMylUtuarWkcMdqaVgtWcxGKsmVzan+6rU2sWzin8Q2j3PMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Entity = _t, Close = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Entity", type text}, {"Close", type number}}, "en-US"),
    // Added [Close_PrevValue] into inner [All] table
    GroupedRows = Table.Group(ChangedType, {"Entity"}, {{"All", each fnShift(_, "Close", 1, null, type number) , type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Ad_Movement = Table.AddColumn(CombinedAll, "Movement", each [Close] - [Close_PrevValue], type number),
    RemovedColumns = Table.RemoveColumns(Ad_Movement,{"Close_PrevValue"})
in
    RemovedColumns