Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Getting the earliest modified Date

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#  ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    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,

     

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello Anonymous 

 

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