Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Date Order

Currently, my order look so;   2018 W1 2018 W10 2018 W11 . . 2018 W2 2018 W21 2018 W22 . . I want 2018 W1,2,3,4,5,6 . . . 2019 W1,2,3,4,5 . . . any ideas how to do?           ...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    Please check the rank column.Make sure it is ranked by YearWeek column.Then use sort by column.

    Rank = RANKX(Table3,Table3[YearWeek],,ASC,Dense)

    Regards,

    Cherie

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous

 

You may sort the column with below steps. Attached the sample file for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xCcAwDAXRVYLrFLbiQG6SFML7rxFwIS6dD+vDy2zRx3O8o62z3t3x+wnH5ZiOW+GJFx7UPZJgCZZgCZZgCZYgCZIgCVuyPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type text}}),
    #"Inserted Text Range" = Table.AddColumn(#"Changed Type", "Text Range", each Text.Middle([Start], 0, 4), type text),
    #"Inserted Text Range1" = Table.AddColumn(#"Inserted Text Range", "Text Range.1", each Text.Middle([Start], 6, 2), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text Range1",{{"Text Range", type number}, {"Text Range.1", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Text Range", Order.Ascending}, {"Text Range.1", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
in
    #"Added Index"

 

Regards,

Cherie

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    thanks for your reply but what if i created 'Year Week' fomat in table like below?

     

    • v-cherch-msft's avatar
      v-cherch-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Hi Anonymous

       

      You may create rank columns as below and then use 'Sort by column'.

      YearWeek = YEAR(Table3[Date])*100+WEEKNUM(Table3[Date],2)
      Rank = RANKX(Table3,Table3[YearWeek],,ASC)

      Regards,

      Cherie

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I followed your advice.

        then i've got an error message saying like the pic below.

        i am not sure what the problem is..!