Forum Discussion
Solved
Reformat text to sort
Hi Community, my problem is the following: My row "KW" (data type text) has to be sorted in asc order. However when the year changes (2024->2025) the order screws up (orange marked). I have found ...
Table.Sort( your_table, (x, y) => Value.Compare( Text.BeforeDelimiter(x[KW], "/") & Text.PadStart(Text.AfterDelimiter(x[KW], "/"), 2, "0"), Text.BeforeDelimiter(y[KW], "/") & Text.PadStart(Text.AfterDelimiter(y[KW], "/"), 2, "0") ) )
Hello technicneo
In this solution I have integrated 2 possible approaches for you.
1) Sorting by date
2) Sorting by index
The Buffer step is important here. This ensures that the desired sorting is retained.
let
//Sample Test Data
StartDate = #date(2024, 1, 1),
EndDate = #date(2025, 12, 31),
DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
Type = Table.TransformColumnTypes(DateTable,{{"Date", type date}}),
ColumnWeek = Table.AddColumn(Type, "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
//The Logic
Index = Table.AddIndexColumn(ColumnWeek, "Index", 1, 1, Int64.Type),
ColumnYear = Table.AddColumn(Index, "Year", each Date.Year([Date]), Int64.Type),
ColumnsYearWeek = Table.CombineColumns(Table.TransformColumnTypes(ColumnYear, {{"Year", type text}, {"Week of Year", type text}}, "de-DE"),{"Year", "Week of Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"KW"),
Duplicates = Table.Distinct(ColumnsYearWeek, {"KW"}),
Sort = Table.Sort(Duplicates,{{"Date", Order.Ascending}}),
Buffer = Table.Buffer(Sort)
in
Buffer
Best regards from Germany
Manuel Bolz
If this post helped you, please consider Accept as Solution so other members can find it faster.
🤝Follow me on LinkedIn