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 relate to the values in the same position, looking something like 

2024-06-27T13:59:46Z, 2025-02-25T11:06:32Z, 2025-01-13T10:09:02Z

or 

2024-06-13T13:11:22Z, 2024-09-16T09:34:19Z, 2024-10-28T09:14:09Z, 2025-01-06T11:31:56Z, 2025-03-10T08:57:28Z, 2025-05-12T07:57:53Z. 

So that for each row, the first value in the Values cell relates to the first date in the Dates cell, the second value relates to the second date, etc.

I want to add another column that has the values sorted in the order given by their associated dates; so, for example, if I have

 

1, 4, 152024-06-27T13:59:46Z, 2025-02-25T11:06:32Z, 2025-01-13T10:09:02Z
92025-06-20T13:59:46Z
5, 72024-11-19T21:41:16Z, 2024-10-10T11:39:56Z
0, 0, 12, 02024-05-15T17:11:33Z, 2024-10-07T12:15:43Z, 2025-01-11T09:34:45Z, 2025-05-12T10:41:43Z

 

I want to get the column

 

1, 4, 152024-06-27T13:59:46Z, 2025-02-25T11:06:32Z, 2025-01-13T10:09:02Z1, 15, 4
92025-06-20T13:59:46Z9
5, 72024-11-19T21:41:16Z, 2024-10-10T11:39:56Z7, 5
0, 0, 12, 02024-05-15T17:11:33Z, 2023-10-07T12:15:43Z, 2025-01-11T09:34:45Z, 2025-05-12T10:41:43Z0, 0, 12, 0

 

How would I go about doing this? Apologies, I'm quite new to Power Bi.

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    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"

     

     

     

3 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    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"

     

     

     

    • Showsni's avatar
      Showsni
      New Member
      1 year ago

      Thanks, looks like it's working perfectly!

  • MCG's avatar
    MCG
    Helper I
    1 year ago

    try also this one:)

    let
    list1=Text.Split(Text.From([Kolumna1]),","),
    list2=Text.Split(Text.From([Kolumna2]),","),
    tabela=Table.FromColumns({list1,list2}),
    tabela_datetime=Table.TransformColumnTypes(tabela,{{"Column2", type datetime}}),
    tabela_sort=Table.Sort(tabela_datetime,{{"Column2", Order.Ascending}}),
    wynik=Text.Combine(tabela_sort[Column1],",")
    in
    wynik

     

    your inital source is two column range with Kolumna1,Kolumna2

    br

    MCG