Forum Discussion
Showsni
1 year agoNew Member
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 ...
- 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"
ronrsnfld
1 year agoSuper User
- 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
1 year agoNew Member
Thanks, looks like it's working perfectly!