Forum Discussion

technicneo's avatar
technicneo
Frequent Visitor
2 years ago
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 a YT-Video Power BI Week Number | Sort Week Number in the right Order | Convert Dates into Week Number in PBI (youtube.com), 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!

Need Help
Show and Tell
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    2 years ago
        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")
        )
    )

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago
        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")
        )
    )
  • ManuelBolz's avatar
    ManuelBolz
    Responsive Resident
    2 years ago

    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