Forum Discussion
Sort columns contains weeks values
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!
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 RemoveDateCopy 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
4 Replies
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 RemoveDateCopy 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
JimmyAnonymous Is that really how your source data looks? Is that a single column of text?
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
