Reformat text to sort

[technicneo]

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 a YT-Video Power BI Week Number | Sort Week Number in the right Order | Convert Dates into Week Number in PBI (..., which explains the error and how to solve it in the table view, but in my case, it has to be do it in Power Query. I have tried to use the Number.FormText function, but i only get errors when using this function. Thanks for any help!

[ManuelBolz]

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

[AlienSx · Accepted solution]

    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")
        )
    )