M Language code to calculate prior period difference for each entity

[Jarrodg]

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 do this with this field. I can get as far as applying an index value to each entity record. Thank you to those of you who assisted me with this!

Here is the script I wrote.

let
Source = Excel.CurrentWorkbook(){[Name="multi_entity_tbl"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Entity", type text}, {"Close", type number}}),
SortedRows = Table.Sort(ChangedType,{{"Entity", Order.Ascending}, {"Date", Order.Ascending}}),
//Group data by entity then create an index number for each entity value
GroupedTable = Table.Group(SortedRows, {"Entity"}, {{"GroupedData", each Table.AddIndexColumn(_,"Index", -1, 1, Int64.Type), type table}}),
// Calculate the difference from the prior period
Difference = Table.AddColumn(GroupedTable, "Movement", each if [Index] = -1 then null else [Close] - GroupedTable{[Index]}[Close], type table),
//Expand the data into the final table view
ExpandedGroupedData = Table.ExpandTableColumn(Difference, "GroupedData", {"Date", "Entity", "Close", "Index"}, {"Date", "Entity.1", "Close", "Index"})

in
ExpandedGroupedData

Here is a screenshot of the script output:

There is an issue with the 'Difference' function that calculates the period movement. I cannot figure out what it is.

Here is an example of the output I am trying to create with this code:

Your assistance with this problem is greatly appreciated. 

Regards,

Jarrod

[dufoq3]

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

[Anonymous · Accepted solution]

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.

[Jarrodg]

Thank you for your assistance, my problem is now resolved. I greatly appreciate it.