Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
colourfullife
Post Partisan
Post Partisan

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?

 

Date Order.PNG

 

 

 

 

Date Order1.PNG

1 ACCEPTED SOLUTION

Hi @colourfullife

 

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-cherch-msft
Employee
Employee

Hi @colourfullife

 

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Date order 1.PNG

Hi @colourfullife

 

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)

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I followed your advice.

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

i am not sure what the problem is..!

error message.PNG

Hi @colourfullife

 

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.