Forum Discussion
asamr
Helper I
Helper IGroup by Date, but sort through timestamps
Dear Community, I have this PQ table. My end goal is to have: Date - SUM(points_earned) - SUM(points_spent) - SUM(points_expired) - SUM(points_balance) I CANT just change the date/time to da...
Use this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc9LDsMgDATQq0SsI+EPNpirRLn/NcqYpmq7GSHryWauq3A5SxNZaTxWDsO71VaFRI6YauU+r4Ipc2q3laHjz/V0uqbiwEJ4dqRWHnB8ME3hhA1zpTzcIaNvKW85JntKnNOAt+Skv5AemGcdGY5lIxe3yvuP/CmTY8Jez7pm8V2GnjKo6IHSbMi+98lmfbKU+34B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, points_earned = _t, points_spent = _t, points_expired = _t, created_at = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"points_earned", Int64.Type}, {"points_spent", Int64.Type}, {"points_expired", Int64.Type}, {"created_at", type datetime}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"created_at", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Date", each Date.From([created_at])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"created_at", "id"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date"}, {{"total_points_earned", each List.Sum([points_earned]), type nullable number}, {"total_points_spent", each List.Sum([points_spent]), type nullable number}, {"total_points_expired", each List.Sum([points_expired]), type nullable number}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "total_points_balanced", each [total_points_earned]-[total_points_spent]-[total_points_expired]) in #"Added Custom1"
asamrHelper I
Helper IHi,
One thing, the point balanced cant be summed.
For every date it needs to be calculated.
Earned - Spent - Expired = Point Balance
Vijay_A_Verma
Most Valuable Professional
Most Valuable ProfessionalUse this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc9LDsMgDATQq0SsI+EPNpirRLn/NcqYpmq7GSHryWauq3A5SxNZaTxWDsO71VaFRI6YauU+r4Ipc2q3laHjz/V0uqbiwEJ4dqRWHnB8ME3hhA1zpTzcIaNvKW85JntKnNOAt+Skv5AemGcdGY5lIxe3yvuP/CmTY8Jez7pm8V2GnjKo6IHSbMi+98lmfbKU+34B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, points_earned = _t, points_spent = _t, points_expired = _t, created_at = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"points_earned", Int64.Type}, {"points_spent", Int64.Type}, {"points_expired", Int64.Type}, {"created_at", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"created_at", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Date", each Date.From([created_at])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"created_at", "id"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Date"}, {{"total_points_earned", each List.Sum([points_earned]), type nullable number}, {"total_points_spent", each List.Sum([points_spent]), type nullable number}, {"total_points_expired", each List.Sum([points_expired]), type nullable number}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "total_points_balanced", each [total_points_earned]-[total_points_spent]-[total_points_expired])
in
#"Added Custom1"