Forum Discussion

Showsni's avatar
Showsni
New Member
1 year ago
Solved

How to sort values within one column by dates within another?

I have one column of Values, separated by commas, where each cell will look something like 1, 4, 15 or 1, 0, 3, 0, 5.   I have another column of Dates, split up in the same way, where the dates ...
  • ronrsnfld's avatar
    1 year ago
    • Convert your two strings into Lists
    • Convert each item in the date time string list into a datetimezone data type
    • Zip the two lists together
    • Sort by the second item in each zipped list
    • Return the first items in the zipped list, combined with the comma delimiter

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY5LCsQwDEOvErJ2wPInJT5HVi29/zXG6XQ6BYNBtqR3HBVUjAq8UhUWa9ybbBMaPsL6TiVVbyxNfALBPVQeFQ06wcEjWPZ60lHHN8ivIP4HXUensv2KkOYxBWEI3EWpcs4q0hF+u5hKDiTXQ+kNybPF+tSXmZNdAh6mb0rMRFQL80fNBFnsWZ+/9Tw/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    
        #"Add Sorted Column" = Table.AddColumn(#"Changed Type", "Sorted", (r)=>
            [a=Text.Split(r[Column1],","),
            b=Text.Split(r[Column2],","),
            c=List.Transform(b, each DateTimeZone.From(_)),
            d=List.Zip({a,c}),
            e=List.Sort(d, each _{1}),
            f=List.Transform(e, each _{0}),
            g=Text.Combine(f,",")][g], type text)
    in
        #"Add Sorted Column"