Forum Discussion

aa_lovee's avatar
aa_lovee
Frequent Visitor
3 years ago
Solved

How Do I Get The Latest Date For Training/Date Column, HELP!

Hey Everyone!

I'm working on a project where I need to show employee training and their Expiration/Due Date. I am almost finished, but I'm having an issue where users have two of the same Training with different due dates (attached). Is there a way that i can remove the training with the old date and keep the latest date without having to delete each row one by one? 

 

Apologies, I've only been doing Power Query/BI for about 2 months now! Any help is appreciated!

Need Help
Tips and Tricks
Tutorial Requests
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    aa_lovee You can first group the data by the columns and identify the max date and then remove everything else as shown in the below code, create a new query and paste it into Advanced Editor.

    let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "jZJRb4IwFIX/SsOzCW1xCI8VN0eEYRR8IT4gIRsZQkJHlv373TIMLcW4J6B895zbc2+aGnVXVcbC8IIQYwtehm/XpCuTYmoZ50Vq7P1dEsBpFL8+H+CZV1fxa2EQkxCBEQWbigFlCYpOKA8vbYWa0bpZbvceKmv00XS8QHnRfsEZiEIBsWcLOo4uWf3ZC9MnXfiFgf1Ksgd/Z7xyFPgntolif9JDXmWct01z/fPvG3C0imkC9KF2ckRr9raTWHKXFepD/Mt50gu2GBPFfzVOQCW1Kdj/1exBrIFasoA9uNDPpWgR+87aoi44R32nUEhNZ77rW+GGJShus7Iu6/eboWBP0Wy00Ik9CiqQmK0XsOPxEEWhQKFlaR0Vdswfm8SVrpZ4OxayuQhov6oaN81UuN4B1YWyhjHRCXdvVYWw2BXiasLq/C11/prwGKZKwoGcjPTz/As=",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [
                    #"Appointment Last Name" = _t,
                    #"Training Code" = _t,
                    #"Training Other" = _t,
                    #"Training Due Date" = _t
                ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            {
                { "Appointment Last Name", type text },
                { "Training Code", type text },
                { "Training Other", type text },
                { "Training Due Date", type date }
            }
        ),
    GroupedRows = 
        Table.Group (
            ChangedType,
            { "Appointment Last Name", "Training Code", "Training Other" },
            {
                {
                    "Transformation",
                    ( CurrentGroup ) =>
                        Table.SelectRows (
                            CurrentGroup,
                            each [Training Due Date] = List.Max ( CurrentGroup[Training Due Date] )
                        ),
                    type table [
                        Appointment Last Name = nullable text,
                        Training Code = nullable text,
                        Training Other = nullable text,
                        Training Due Date = nullable date
                    ]
                }
            }
        ),
    RemovedOtherColumns = Table.SelectColumns ( GroupedRows, { "Transformation" } ),
    ColumnNames = Table.ColumnNames ( RemovedOtherColumns[Transformation]{0} ),
    ExpandedCount = 
        Table.ExpandTableColumn (
            RemovedOtherColumns,
            "Transformation",
            ColumnNames,
            ColumnNames
        )
in
    ExpandedCount

     

1 Reply

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    aa_lovee You can first group the data by the columns and identify the max date and then remove everything else as shown in the below code, create a new query and paste it into Advanced Editor.

    let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "jZJRb4IwFIX/SsOzCW1xCI8VN0eEYRR8IT4gIRsZQkJHlv373TIMLcW4J6B895zbc2+aGnVXVcbC8IIQYwtehm/XpCuTYmoZ50Vq7P1dEsBpFL8+H+CZV1fxa2EQkxCBEQWbigFlCYpOKA8vbYWa0bpZbvceKmv00XS8QHnRfsEZiEIBsWcLOo4uWf3ZC9MnXfiFgf1Ksgd/Z7xyFPgntolif9JDXmWct01z/fPvG3C0imkC9KF2ckRr9raTWHKXFepD/Mt50gu2GBPFfzVOQCW1Kdj/1exBrIFasoA9uNDPpWgR+87aoi44R32nUEhNZ77rW+GGJShus7Iu6/eboWBP0Wy00Ik9CiqQmK0XsOPxEEWhQKFlaR0Vdswfm8SVrpZ4OxayuQhov6oaN81UuN4B1YWyhjHRCXdvVYWw2BXiasLq/C11/prwGKZKwoGcjPTz/As=",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [
                    #"Appointment Last Name" = _t,
                    #"Training Code" = _t,
                    #"Training Other" = _t,
                    #"Training Due Date" = _t
                ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            {
                { "Appointment Last Name", type text },
                { "Training Code", type text },
                { "Training Other", type text },
                { "Training Due Date", type date }
            }
        ),
    GroupedRows = 
        Table.Group (
            ChangedType,
            { "Appointment Last Name", "Training Code", "Training Other" },
            {
                {
                    "Transformation",
                    ( CurrentGroup ) =>
                        Table.SelectRows (
                            CurrentGroup,
                            each [Training Due Date] = List.Max ( CurrentGroup[Training Due Date] )
                        ),
                    type table [
                        Appointment Last Name = nullable text,
                        Training Code = nullable text,
                        Training Other = nullable text,
                        Training Due Date = nullable date
                    ]
                }
            }
        ),
    RemovedOtherColumns = Table.SelectColumns ( GroupedRows, { "Transformation" } ),
    ColumnNames = Table.ColumnNames ( RemovedOtherColumns[Transformation]{0} ),
    ExpandedCount = 
        Table.ExpandTableColumn (
            RemovedOtherColumns,
            "Transformation",
            ColumnNames,
            ColumnNames
        )
in
    ExpandedCount