Forum Discussion
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 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
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.
3 Replies
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.
Thank you for your assistance, my problem is now resolved. I greatly appreciate it.
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
