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"
MCG
1 year agoHelper I
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