Sorting Week Number

[erhan_79]

Hi there ;

i need your kind and small help about below issue 

i have table vision and in table vision there is column with week numbers as below .it starts from W-1 to W-53.this column's data type is text.When i want to sort from W-1 to W-53 , system is not sorting as i want .As you see below it is sorting wrongly  : 

when i use sorting function on the top of column it is not sorting properly , i want to sort as below , how can i solve this problem , could you pls help  ?

W-1

W-2

W-3

W-4

...

...

...

thanks in advanve for your supports 

[az38]

Hi @erhan_79 

you need to use leading 0 for week number

W-01

W-02

..

W-10

for example you can use DAX

= IF(WEEKNUM([Date]) < 10, CONCATENATE("0", WEEKNUM([Date])), WEEKNUM([Date]))

[erhan_79]

 dear  @az38  ,

thanks for your reply but , is not there any other way ,leading 0 for week number else ?

[az38]

@erhan_79 

you also can create a new column

WeekNumNew = IF(
LEN([WeekNum]) = 3, 
CONCATENATE(LEFT([WeekNum], 2), CONCATENATE("0", RIGHT([WeekNum], 1))),  
[WeekNum]
)

[az38]

@erhan_79 

it depends on what do you have. there are a lot of solution.

how do you get your WeekName field?

[erhan_79]

dear @az38 

i got week name with below date dataset formula : 

let
Source = Query1(#date(2020, 1, 1), #date(2021, 12, 31), 1),
#"Inserted Start of Week" = Table.AddColumn(Source, "Start of Week", each Date.StartOfWeek([Date]), type date),
#"Added Custom" = Table.AddColumn(#"Inserted Start of Week", "IsToday", each if Date.IsInCurrentDay([Date]) then 1 else 0),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"IsToday", type logical}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "IsCurrentYear", each if Date.IsInCurrentYear([Date]) then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"IsCurrentYear", type logical}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "IsCurrentYearTillStartDateOfCurrentWeek", each if [IsCurrentYear] and [Date] < Date.StartOfWeek(Date.From(DateTime.LocalNow())) then 1 else 0),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"IsCurrentYearTillStartDateOfCurrentWeek", type logical}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type2", "IsYTD", each if [IsCurrentYear] and [Date] <= Date.AddDays(Date.From(DateTime.FixedLocalNow()),-1) then 1 else 0),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom3",{{"IsYTD", type logical}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type3", "IsCurrentWeek", each if Date.IsInCurrentWeek([Date]) then 1 else 0),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom4",{{"IsCurrentWeek", type logical}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type4", "WeekName", each Text.Combine({"W-", Text.From([Week Number], "en-US")}), type text)
in
#"Inserted Merged Column"

jus ti would like to sort without adding "0" , if it is possible 

[az38 · Accepted solution]

@erhan_79 

ok. as you use Power Query you can replace #"Inserted Merged Column" step to

#"Inserted Merged Column" =  Table.AddColumn(#"Changed Type4", "WeekName", each if [Week Number] > 9 then Text.Combine({"W-",Text.From([Week Number], "en-US")}) else Text.Combine({"W-0", Text.From([Week Number], "en-US")}))

[erhan_79]

thank you very much @az38 

perfect! it is working  now