Add Custom Column in GroupBy All Rows Column Based on Max Date

[cflynn_29]

I have a "Test" Column which has the previous Table[s] that has one through a Groupby All Rows fucntion.

In each of these nest grouped tables there is an ID number that in each group changes periodically to a new ID number based on the date.

What i would like to do is find the Max Date in each Group and create a column that returns only that ID number in the example below the max date is 1/23/2023 and the corresponding ID number is 33636644 so the new column for this group would just contain that number.

Any assitance would be very helpful

Thank you

[cflynn_29]

This worked out extremly well, do have any resources applicable to Nested Table Groups and Vairables? Anything would be helpful.

Thanks Again!

[edhans]

I wrote a blog on this a few years ago that does a deeper dive into adding columns for a Group By operation here - Return Row Based on Max Value From One Column when Grouping — ehansalytics

As far as variables go, they are very simlar to DAX variables with few exceptions:

1. They need to go in the right place, which is usually after the each statement before you start with the main function.
2. You start them with let and close them with in. DAX variables don't start with anything and are closed with RETURN.
3. If you have 2 or more variables as I did, they all need to end with a comma except for the last one. DAX variables never end with a comma.

But just like DAX variables, they can build on any previous variables, as I did in this example, or do their own logic.

[edhans · Accepted solution]

This logic will work for you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLLL0oBUkb6Rob6RgZGxkqxOtFKRigSRggJYxQJY4SECVDE3RcibI4QNkUSNkUImyEJm0GFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Model = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Model"}, 
            {
                {
                    "All Rows", 
                    each 
                        let
                            varMaxDate = Table.Max(_,"Date")[Date],
                            varID = Table.SelectRows(_, each [Date] = varMaxDate)[ID]{0}
                        in
                    Table.AddColumn(_, "Max ID", each varID),
                    type table [ID=nullable text, Model=nullable text, Date=nullable date, Max ID = text]
                }
            }
        )
in
    #"Grouped Rows"

My original data is:

I want to return ID 3 for Ford and ID 5 for GM as those have the max dates on the Model field where I grouped by it.

You can see it works for Ford, and does for GM as well.

Here is what I did:

1. The grouped by All Rows table is always referenced by the _ char.
2. I modified the default All Rows, which is normally just All Rows, each _, etc...
3. I added two variables.
   1. varMaxDate finds the max date in the "current" table. So for Ford it found Feb 23, 2023.
   2. varID filtered the "current" table for that date, and returned the first record of the ID field in that table, which for Ford was 3.
4. Then Table.AddColumn add the "Max ID" field to the All Rows _ table.
5. Note the type table step must include a type for the new column or it will not show up.

One comment - I cannot guarantee performance on larger datasets. A few thousand rows might work well. Tens of thousands, hundreds of thousands, or millions many not, and in those cases DAX is really the way to go there. If you need this on tens of millions of rows, it makes an excellent use case for a Calculated Column. Power Query just isn't super performant with table scans. DAX was designed for it.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If you need more help, please post usable data. I cannot work with images for source data.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[cflynn_29]

So i used those steps and changed out ID = "Meter" and Date ="Bill Date" and i got an error for the added column MaxMeter

And here is the M code:

let
    Source = RCC_Electrical_Update,
    #"Grouped Rows" = Table.Group(Source, {"ACCOUNT", "ADDRESS", "SERVICE NUMBER"}, 
    {
        {
            "All Rows",
            each
                let
                    varMaxDate = Table.Max(_,"Date")[Bill Date],
                    varMeter = Table.SelectRows(_, each [Bill Date] = varMaxDate)[Meter]{0}
                in
            Table.AddColumn(_, "MaxMeter", each varMeter),
            type table [APPLICATION=nullable number, RECALC=nullable number, ACCOUNT=nullable text, PREMISE=nullable text, CIS NUMBER=nullable text, NAME=nullable text, ADDRESS=nullable text, METER=nullable text, DEVICE SIZE=nullable text, CIRCUIT=nullable text, BILL DATE=nullable date, PREVIOUS READ DATE=nullable date, PRESENT READ DATE=nullable date, DAYS SERVICE=nullable number, TAX CODE=nullable text, TAX TYPE=nullable text, SERVICE NUMBER=nullable text, SERV RANK SEQ=nullable text, MAX ITEM SEQ=nullable number, CLASS=nullable text, RATE=nullable text, ITEM CODE=nullable text, ITEM CLASS=nullable text, ITEM RATE=nullable text, ITEM RATE DRIVER=nullable text, STATE ENERGY EXEMPT=nullable number, DMON USE=nullable number, DMMD USE=nullable number, DMOF USE=nullable number, DMON AMT=nullable number, DMMD AMT=nullable number, DMOF AMT=nullable number, KWON USE=nullable number, KWMD USE=nullable number, KWOF USE=nullable number, KWON AMT=nullable number, KWMD AMT=nullable number, KWOF AMT=nullable number, KWH USE=nullable number, KW USE=nullable number, KWH AMT=nullable number, KW AMT=nullable number, MAX DEMAND=nullable number, NET CONSUMPTION=nullable number, REG1USE=nullable number, MULTIPLIER=nullable number, NET CHARGE=nullable number, STATE ENERGY=nullable number, RELIABILITY CHG=nullable number, SVC CHG=nullable number, EUUT CHG=nullable number, PB CHG=nullable number, NAC CHG=nullable number, RENEWEABLE ENG CHG=nullable number, WUUT CHG=nullable number, WCON CHG=nullable number, TOTAL=nullable number, NET TOTAL=nullable number, NAICS=nullable text, MaxMeter = text]
        }
    }
)
in
    #"Grouped Rows"

I am not sure where it went wrong, there are no syntax errors.