Sort columns contains weeks values

[Anonymous]

Hey guys,

I Have a list of unsorted weeks, here is an example:

02/08 - 08/08
06/09 - 12/09
09/08 - 15/08
13/09 - 19/09
16/08 - 22/08
23/08 - 29/08
26/07 - 01/08
30/08 - 05/09

I want to pivot the column to make the list the headers, and after that I want to sort them ascending by the dates.

How you will recommend to sort them? Thanks!

[Jimmy801 · Accepted solution]

Hello @Anonymous 

try this. Add a column where you extract the first part, transform it to a date, sort it and then remove the date column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc1LDsAgCEXRrRjHbeQTrazFuP9t+FqgI5LLCaxVSRrNcheamHVfKKORoTA25sXccE/DGsbS8HAjkkY0iv0F5nl/cRal+N6/O/sA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    AddDateColumn = Table.AddColumn(ChangeType, "Custom", each Date.From(Text.Split([Column1]," - "){0})),
    SortDate = Table.Sort(AddDateColumn,{{"Custom", Order.Ascending}}),
    RemoveDate = Table.RemoveColumns(SortDate,{"Custom"})
in
    RemoveDate

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Greg_Deckler]

@Anonymous Is that really how your source data looks? Is that a single column of text?

[Anonymous]

Next to this coulmn I have a coulmn that contains number of users for each week:

[HotChilli]

I would advise against using pivot.

If you use the matrix visualisation you can put the Week in the Columns section.

Before doing that, in Power Query, duplicate the column and , using the duplicate, split the column on '-'. Rename one of the columns 'WeekStart', make it a date type.  Sort the data on that column.

In the matrix, you might need 'Sort by Column' to get the order right (use WeekStart as the sort column for Week)

Good luck