Forum Discussion

asamr's avatar
asamr
Icon for Helper I rankHelper I
4 years ago
Solved

Group 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 date and group by. 

I have several entries on the same day.

 

I need to somehow Group by Date and sort it through the ASC timestamps so my end result be correct. 

 

How can I do this?

 

 

 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    You will need to first sort and then do the grouping

    I have made a small mock-up. Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZDRDcQwCENXqfJdqZgESLJK1f3XuJheT+r9WAgewuY8C8pemupSQ1/ajXXHYP9oh4rqNma1cu1n4QzIHbelo/ZU/NGRdF1dda6osAyqDcL1QCeNDTIViTf2azII8oMKl5vXL98nPHkaqINblkvCe67xxuXB04hTh0dmzCOw9I7bO35ROWzCoedLzPiSeP1FnqT8gmcwGDXShNWE9YZjQst1fQA=", 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, points_balance = _t, created_at = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"points_earned", Int64.Type}, {"points_spent", Int64.Type}, {"points_expired", Int64.Type}, {"points_balance", 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}, {"total_points_balance", each List.Sum([points_balance]), type nullable number}})
in
    #"Grouped Rows"

     

    • asamr's avatar
      asamr
      Icon for Helper I rankHelper I
      4 years ago

      Hi,

      One thing, the point balanced cant be summed. 
      For every date it needs to be calculated. 

      Earned - Spent - Expired = Point Balance

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

        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"