Forum Discussion
To show max value based on condition within time period
- 2 years ago
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "ddBBDsQgCIXhq0xc1wQeqJWrNL3/NUY70tLFJO6+/BA8jsTMaUvEmTlDPgwrPZ2bA5c/gLsoVmoEcVCjEqE5NKPXqP0ZtQDABM2MC9igEbrDKCgASR6nQOcO1ffyBezFuhx5vAndiCPUBSCDRGheVJNfISLzr+gB7AFwAy44vw==", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Phone = _t, Date = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Phone", Int64.Type}, {"Date", type datetime}}, "en-GB" ), #"Grouped Rows" = Table.Group( #"Changed Type", {"Phone"}, {{"Rows", each _, type table [Phone = nullable number, Date = nullable datetime]}} ), Process = (tbl) => let #"Sorted Rows" = Table.Sort(tbl, {{"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn( #"Added Index", "Group", each List.Accumulate( {0 .. [Index]}, 1, (state, current) => if current = 0 then state else if #"Added Index"{current}[Date] - #"Added Index"{current - 1}[Date] > #duration(7, 0, 0, 0) then state + 1 else state ) ), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"Phone", "Date", "Group"}), #"Grouped Rows1" = Table.Group( #"Removed Other Columns", {"Group"}, { { "Rows", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table [ Phone = nullable number, Date = nullable datetime, Group = number, Index = Int64.Type ] }, {"Max", each Table.RowCount(_), Int64.Type} } ), #"Expanded Rows" = Table.ExpandTableColumn( #"Grouped Rows1", "Rows", {"Phone", "Date", "Index"} ) in #"Expanded Rows", #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Processed", each Process([Rows])), #"Expanded Processed" = Table.ExpandTableColumn( #"Added Custom", "Processed", {"Group", "Date", "Index", "Max"}, {"Group", "Date", "Index", "Max"} ), #"Removed Other Columns" = Table.SelectColumns( #"Expanded Processed", {"Phone", "Group", "Date", "Index", "Max"} ) in #"Removed Other Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
That's the "Index" column in my result. Feel free to rename it.
Thank you very much for your help and the code.
This works and shows the expected results!
The only thing is that it takes significant amount of time to work (about 3 minutes for a base i have currently of 32 000 rows.
I've realized that it takes the time at the step "Expanded Processed":
Is there any chance to optimize it ?
Thank you again !
- lbendlin2 years ago
Super User
Try changing
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Processed", each Process([Rows])),to
#"Added Custom" = Table.Buffer(Table.AddColumn(#"Grouped Rows", "Processed", each Process([Rows]))),- Dimi_22072 years ago
Helper I
Thank you! Made the change.
Unfortunately, still refresh takes from 5 to 10 minutes.
Is it possible to do something similar in DAX, may be it will be faster ?
- lbendlin2 years ago
Super User
your sample file only has 12 rows. Please provide a sample file that illustrates the issue.