Forum Discussion
Temporal Independence List.Accumulate (maybe)
I have two columns Value and Date. I need to identify dates that are not temporally independent so the values can be grouped (Average). For this project, temporary independence is defined as 90 days.
Example Data table
Value,Date
139,1/19/2022
140,10/18/2021
140,7/20/2021
141,4/23/2021
140,1/19/2021
140,1/19/2021
141,11/23/2020
141,1/23/2020
137,10/23/2019
141,7/17/2019
A new column, Column C is the goal.
I think this can be done with the List. Accumulate function. The basic List. Accumulate syntax is
if state - current <=90 then state else current
This is what I currently have
= Table.AddColumn(#"Reordered Columns", "Custom", each List.Accumulate(
List.Range(#"Reordered Columns"[Date],[Index],1),{List.Max(#"Reordered Columns"[Date])},
(state, current) =>
if List.Last(state) - current <= 90 then List.Max(state) else current
))
This code only returns the current value.
The date column was transformed to Int64.Type prior to the List. Accumulate step.
DAX is not an option for this project. I am seeking an M-code solution.
Many thanks
Hi Anonymous ,
that is a surprise to me as well.
Didn't realize that this is working differently. Looking like a bug in Excel to me, tbh.= Table.Combine(Table.Group(Source, "Date", {"n", each Table.AddColumn(_,"Date_Group",(x)=>List.Max([Date]))}, 0, (x,y)=> Byte.From(Duration.Days(x-y)>90))[n])
6 Replies
Hi Anonymous ,
you can add this logic into the grouping operation in Power Query like so:= Table.Group(#"Changed Type", {"Date"}, {{"Group", each _}}, 1, (state, current)=> Number.From(Number.From(state[Date]-current[Date])>90))I have described the mechanism in this blogpost: Table.Group: Exploring the 5th element in Power BI and Power Query – The BIccountant
Please also check out the solution in the file attached.
This is interesting. We seem to have stumbled into a different issue. The solution provided is a half solution. It works in PowerBI's Power Query Editor but it does not work in Excel's Power Query Editor. Maybe I should have mentioned I was working in Excel and not PowerBI. I simply assumed both were using the same M-code engine.
Results in PowerBI’s Power Query Editor
Results in Excel’s Power Query Editor
Thanks
LAS
= Table.Combine(Table.Group(Source, "Date", {"n", each Table.AddColumn(_,"Date_Group",(x)=>List.Max([Date]))}, 0, (x,y)=> Byte.From(Duration.Days(x-y)>90))[n])
How do you add additional grouping values? For example with the table below, Group By Date Group and Customer_ID. What if there were three or more Group By items, ex Date Group, Customer_ID, Product_Group?
let
Source = #table({"Customer_ID", "Date", "Value"}, {
{"1620A","8/23/2017","4.04"},{"1620A","8/23/2017","16.16"},{"1620A","8/23/2017","0"},{"1620A","4/12/2017","1.01"},{"1620A","4/11/2017","3.03"},{"1620A","4/11/2017","19.19"},{"1620A","11/29/2016","3.03"},{"1620A","11/29/2016","7.07"},{"1620A","7/12/2016","31.31"},{"1620A","10/21/2015","27.27"},{"1620A","1/14/2009","43.43"},{"1620A","7/21/2008","86.25"},{"716C","10/23/2019","33.33"},{"3703M","10/23/2019","54.12"},{"716C","7/17/2019","28.99"},{"3703M","7/17/2019","60.71"},{"716C","4/17/2019","59.78"},{"3703M","4/17/2019","69.77"},{"716C","1/23/2019","30"},{"3703M","1/23/2019","52.38"},{"716C","10/30/2018","93.94"},{"3703M","10/30/2018","61.25"},{"716C","7/17/2018","50.51"},{"3703M","7/17/2018","69.23"},{"716C","4/19/2018","0"},{"3703M","4/19/2018","64.94"},{"716C","1/17/2018","15"},{"3703M","1/17/2018","65.52"},{"716C","10/19/2017","70.71"},{"3703M","10/19/2017","83.12"},{"716C","7/26/2017","46.05"},{"3703M","4/27/2017","20.2"},{"716C","4/26/2017","75.56"},{"3703M","3/8/2017","48.48"},{"716C","3/8/2017","74.19"}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
#"Changed Type"
Hi Anonymous ,
that is a surprise to me as well.
Didn't realize that this is working differently. Looking like a bug in Excel to me, tbh.