Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Leataloneshimmy
Frequent Visitor

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. 

Leataloneshimmy_1-1675792085744.png

 

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

 

2 ACCEPTED SOLUTIONS
ImkeF
Super User
Super User

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

View solution in original post

wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1675909040683.png

 

= 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])

View solution in original post

6 REPLIES 6
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1675909040683.png

 

= 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.

ImkeF
Super User
Super User

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

ImkeF
Super User
Super User

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

 

Leataloneshimmy_3-1675867947930.png

 

Results in Excel’s Power Query Editor

Leataloneshimmy_4-1675867947932.png

Thanks

LAS

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors