Forum Discussion
Anonymous
2 years agoNot applicable
Help needed with creating SelectRows and Group function
Hi, I need help with creating a function. This is my sample data: session_start session_user Daily_sum 9/20/2023 Person 1 4 9/22/2023 Person 1 5 9/25/2023 Person 1 8 ...
- Anonymous2 years ago
Thank you for the reply!
I only want to take into account past days and not today so Date.IsInPreviousNDays should serve my purpose.I've been able to refine my function as follows:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk4tLs7Mz4svLkksKlHSgfNLi1OLgFyXxMycyvji0lylWJ1oJUt9IwMgMjIGygSkFhXn5ykYApkmMEkjLJKmMElTLJIWMEljNEkjINMYl04jZGPNsEjCHWSORdJcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Promoted headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"session_start", type date}, {"session_user", type text}, {"Daily_sum", Int64.Type}}), CustomFunction = (n as number, user as text, initialN as number) as table => let FilteredRows = Table.SelectRows(#"Changed column type", each [session_user] = user and Date.IsInPreviousNDays([session_start], n)), GroupedRows = Table.Group(FilteredRows, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}), Result = if Table.IsEmpty(GroupedRows) or List.Min(GroupedRows[Sum]) < 10 then Table.AddColumn(Table.AddColumn(GroupedRows, "Final N Value", each n), "First Try", each n = initialN) else @CustomFunction(n-1, user, initialN) in Result, Users = List.Distinct(#"Changed column type"[session_user]), DaysToFetch = 60, FinalResult = Table.FromRecords(List.Combine(List.Transform(Users, each Table.ToRecords(CustomFunction(DaysToFetch, _, DaysToFetch))))), Custom = Table.AddColumn(FinalResult, "Custom", each Date.AddDays( Date.From(DateTime.FixedLocalNow()),-[Final N Value]), type date), #"Custom 1" = Table.AddColumn(#"Custom", "not_valid_anymore", each Date.AddDays( Date.From(DateTime.FixedLocalNow()), if [First Try] = true then 0 else DaysToFetch - [Final N Value]), type date) in #"Custom 1"This pretty much gets me what I need.
lbendlin
2 years agoSuper User
Danger Danger Danger.
Date.IsInPreviousNDays does not include Today !!!
Use a different function.
You also will want to group by person a lot earlier.
- Anonymous2 years agoNot applicable
Thank you for the reply!
I only want to take into account past days and not today so Date.IsInPreviousNDays should serve my purpose.I've been able to refine my function as follows:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk4tLs7Mz4svLkksKlHSgfNLi1OLgFyXxMycyvji0lylWJ1oJUt9IwMgMjIGygSkFhXn5ykYApkmMEkjLJKmMElTLJIWMEljNEkjINMYl04jZGPNsEjCHWSORdJcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Promoted headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"session_start", type date}, {"session_user", type text}, {"Daily_sum", Int64.Type}}), CustomFunction = (n as number, user as text, initialN as number) as table => let FilteredRows = Table.SelectRows(#"Changed column type", each [session_user] = user and Date.IsInPreviousNDays([session_start], n)), GroupedRows = Table.Group(FilteredRows, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}), Result = if Table.IsEmpty(GroupedRows) or List.Min(GroupedRows[Sum]) < 10 then Table.AddColumn(Table.AddColumn(GroupedRows, "Final N Value", each n), "First Try", each n = initialN) else @CustomFunction(n-1, user, initialN) in Result, Users = List.Distinct(#"Changed column type"[session_user]), DaysToFetch = 60, FinalResult = Table.FromRecords(List.Combine(List.Transform(Users, each Table.ToRecords(CustomFunction(DaysToFetch, _, DaysToFetch))))), Custom = Table.AddColumn(FinalResult, "Custom", each Date.AddDays( Date.From(DateTime.FixedLocalNow()),-[Final N Value]), type date), #"Custom 1" = Table.AddColumn(#"Custom", "not_valid_anymore", each Date.AddDays( Date.From(DateTime.FixedLocalNow()), if [First Try] = true then 0 else DaysToFetch - [Final N Value]), type date) in #"Custom 1"This pretty much gets me what I need.