How to Determine if a Date is the Maximum in a Year (Dates Grouped by Categories)

[zerosugar]

Hi, in Power Query, I'm trying to figure out how to get the maximum date in a user-defined series. Once I have that maximum date, I will use that information to determine conditional logic for how a new column will be created.

This is my data:

Account Type Date Year Value
Checking Account Balance 3/31/2018 2018 30000
Checking Account Balance 6/30/2018 2018 31235
Checking Account Balance 9/30/2018 2018 33400
Checking Account Balance 12/31/2018 2018 40120
Checking Account Balance 3/31/2019 2019 50700
Checking Account Balance 6/30/2019 2019 42000
Checking Account Balance 9/30/2019 2019 42005
Checking Account Balance 12/31/2019 2019 50000
Checking Account Balance 3/31/2020 2020 53000
Checking Account Balance 6/30/2020 2020 54050
Checking Account Outflow 3/31/2018 2018 872
Checking Account Outflow 6/30/2018 2018 1260
Checking Account Outflow 9/30/2018 2018 1428
Checking Account Outflow 12/31/2018 2018 1678
Checking Account Outflow 3/31/2019 2019 1259
Checking Account Outflow 6/30/2019 2019 1590
Checking Account Outflow 9/30/2019 2019 1737
Checking Account Outflow 12/31/2019 2019 803
Checking Account Outflow 3/31/2020 2020 1378
Checking Account Outflow 6/30/2020 2020 953
Checking Account Inflow 3/31/2018 2018 1188
Checking Account Inflow 6/30/2018 2018 1347
Checking Account Inflow 9/30/2018 2018 1206
Checking Account Inflow 12/31/2018 2018 1006
Checking Account Inflow 3/31/2019 2019 1576
Checking Account Inflow 6/30/2019 2019 1593
Checking Account Inflow 9/30/2019 2019 1014
Checking Account Inflow 12/31/2019 2019 1410
Checking Account Inflow 3/31/2020 2020 950
Checking Account Inflow 6/30/2020 2020 1399
Savings Account Balance 3/31/2018 2018 80000
Savings Account Balance 6/30/2018 2018 84000
Savings Account Balance 9/30/2018 2018 88200
Savings Account Balance 12/31/2018 2018 92610
Savings Account Balance 3/31/2019 2019 97240.5
Savings Account Balance 6/30/2019 2019 102102.525
Savings Account Balance 9/30/2019 2019 107207.6513
Savings Account Balance 12/31/2019 2019 112568.0338
Savings Account Balance 3/31/2020 2020 118196.4355
Savings Account Balance 6/30/2020 2020 124106.2573
Savings Account Outflow 3/31/2018 2018 872
Savings Account Outflow 6/30/2018 2018 1260
Savings Account Outflow 9/30/2018 2018 1428
Savings Account Outflow 12/31/2018 2018 1678
Savings Account Outflow 3/31/2019 2019 1259
Savings Account Outflow 6/30/2019 2019 1590
Savings Account Outflow 9/30/2019 2019 1737
Savings Account Outflow 12/31/2019 2019 803
Savings Account Outflow 3/31/2020 2020 1378
Savings Account Outflow 6/30/2020 2020 953
Savings Account Inflow 3/31/2018 2018 1188
Savings Account Inflow 6/30/2018 2018 1347
Savings Account Inflow 9/30/2018 2018 1206
Savings Account Inflow 12/31/2018 2018 1006
Savings Account Inflow 3/31/2019 2019 1576
Savings Account Inflow 6/30/2019 2019 1593
Savings Account Inflow 9/30/2019 2019 1014
Savings Account Inflow 12/31/2019 2019 1410
Savings Account Inflow 3/31/2020 2020 950
Savings Account Inflow 6/30/2020 2020 1399

The data has two accounts: Checkings and Savings. Each account records Balance, Inflows, and Outflows. The Inflows and Outflows are RANDBETWEEN(), so don't worry if prev balance + inflow - outflow != current balance.

The issue with this data occurs when I want to aggregate at the year-level. If I have a power bi visual that aggregates at the year-level, it will add up (or average or max, etc.) the quarterly values. That is fine for the inflows and outflows - I want to see the SUM of inflows and outflows in a year. However, Balances need to be treated differently. Only the last balance value of the year should be shown. So, it shouldn't be aggregated.

Different years can have different ending balance months - 2018 and 2019 end in December, but 2020 ends in July.

In Power Query, is it possible to create a primary key of "Account", "Type", and "Year", but if the "Type" is Balance, then only create the key for the last date in that year. For the other "Types", we can create the primary key for every revenue date.

Something like this:

Note, no key for non-year-ending balances.

Is this possible in Power Query? I tried using the Group By function, but that won't help me create this Primary Key column - it just aggregates at the level of maximum date in the Account-Type combination, which isn't what I want. I want all the original columns, but just have a key for the ones that I actually plan on displaying/aggregating. Thank you!

[m_alireza]

Hi @zerosugar , 

Yes, it actually is possible in Power Query using the Group By function. You just have to add the "All Rows" Operation and expand that to get your original columns, then get the key based on your condition specified. 

Copy and paste this code in your advanced query to see the steps and adjust as needed to match your situation: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZW7TsNAEEV/BbmO7J3ZdwlUVBSUUQrLCg8ROQUJ/D6GGOH17MbXUaRViqOdM77x3W6ru/bQ9t2+2lSaGqUbVhSGH+Oh1fAZzvvXfff+1r/c3Hbd8dyfqt0mQVWj3Bwl1hZD4xzVBrqVGuIZahQxhl5c4wX9Oazyq1wnqGF4TTGDImv6c00Gxm4dXX/3Mh5Wr3quCWqULaKP59Pz4fhVSFPwDICZLBE75MpMlMhwgIYVSSLnMVIEidjGFZ5T0sY1nlPSa7/Cc0IGpXHNaRBIQwvKRCja4pUP/ZX8EIXijf9gLj/aFLeTgCI+rBwyqkyPwkAZHusBMJ8dYKu56CgyuOMUNFRMq3BMnz/AZXJDOhb/WNd7LIyvyqf2cwA/VtRYMCg5z04IjJCZ8ER2BJEiPdGzUbXFTZMc8PCtLWO4jJFn5WtnSePSU354bbpQK60DrJ6EgwJFVxttcf2E5yHOrmbrS/Mj5bbAlbttGSxU2+KkpWYDFWWxgY6y10BHWWugo2g1TDFTapii7LQch1Tada7caItcodCW5iz1GeYn6wzzk22G+ckyw/xkl0F+ssogvUyTCW73DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Date = _t, Year = _t, Value = _t, Account = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Date", type date}, {"Year", Int64.Type}, {"Value", type number}, {"Account", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account", "Type", "Year"}, {{"MaxDate", each List.Max([Date]), type nullable date}, {"AllRows", each _, type table [Type=nullable text, Date=nullable date, Year=nullable number, Value=nullable number, Account=nullable text]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Date", "Value"}, {"Date", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Year", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Key", each if [MaxDate] = [Date] and [Type] ="Balance" then [Account] &";" & [Type] & ";" & [Year] else if [Type] <> "Balance" then [Account] &";" & [Type] & ";" & [Year] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"MaxDate"})
in
    #"Removed Columns"

 Sample output: