Getting the earliest modified Date

[Nusc]

Hi,

I have a file that lists the Field Ticket Number and Modified Date. I would like to create a new field called FirstModifiedDate.

One ticket number can have multiple line items:

Ticket#  ModifiedDate

181234 12/5/2019

181234 12/4/2019

181234 12/5/2019

181235 12/5/2019

181235 12/4/2019

181235 12/3/2019

I would like my output as follows:

Ticket#  ModifiedDate FirstModifiedDate

181234 12/5/2019 12/4/2019 

181234 12/4/2019 12/4/2019 

181234 12/5/2019 12/4/2019 

181235 12/5/2019 12/3/2019

181235 12/4/2019 12/3/2019

181235 12/3/2019 12/3/2019

What do I need to do in Power Query?

[v-lid-msft · Accepted solution]

Hi @Nusc ,

We can use group for one time and then expand the result to meet your requirement:

All the quries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQwNDI2UdJRMjTSN9U3MjC0VIrVQRU2wS6MrtoUrzC6IVBhY6hwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Ticket#" = _t, ModifiedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket#", Int64.Type}, {"ModifiedDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket#"}, {{"Data", each _, type table [#"Ticket#"=number, ModifiedDate=date]}, {"FirstModifiedDate", each List.Min([ModifiedDate]), type date}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"ModifiedDate"}, {"Data.ModifiedDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Data",{{"Data.ModifiedDate", "ModifiedDate"}})
in
    #"Renamed Columns"

Best regards,

[Nusc]

I had no idea this was possible....I just did a calculated column.

[Jimmy801]

Hello @Nusc 

for this you need some coding and use a Table.Group with List.Min function and a NestedJoin to join the result to your original table.

Here the complete solution

let
	Source = #table
	(
		{"Ticket#","ModifiedDate"},
		{
			{"181234","43597"},	{"181234","43567"},	{"181234","43597"},	{"181235","43597"},	{"181235","43567"},	{"181235","43536"}
		}
	),
    ToDate = Table.TransformColumns
    (
        Source,
        {
            {
                "ModifiedDate",
                each Date.From(Number.From(_)),
                type date
            }
        }
    ),
    Group = Table.Group
    (
        ToDate,
        {"Ticket#"},
        {
            {
                "MaxDate",
                each List.Min(_[ModifiedDate]),
                type date
            }
        }
            
    ),
    NestedJoin = Table.NestedJoin
    (
        ToDate,
        "Ticket#",
        Group,
        "Ticket#",
        "MaxDate"

    ),
    Expand = Table.ExpandTableColumn(NestedJoin, "MaxDate", {"MaxDate"}, {"MaxDate"})
in
	Expand

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Nusc]

Was I not clear?