Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @Leataloneshimmy ,
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
= 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])
= 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"
That is a beautiful nest. The code works (in Excel) and I thank you.
Hi @Leataloneshimmy ,
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Leataloneshimmy ,
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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