Forum

Power Query: Sort D...
 
Notifications
Clear all

Power Query: Sort Date Headers in Chronological Order.

2 Posts
2 Users
0 Reactions
341 Views
(@jason-wier)
Posts: 23
Eminent Member
Topic starter
 

G’day Mates:

I have a worksheet export that has dates listed in the header row that are not in chronological order.

Can someone provide insight on what I can do to have the dates listed chronologically by MMM-YY?

I’ve been trying to work more with PowerQuery, so if possible, can you please the steps on how I would do this in PQ?

Thank you - This will be an incredible time saver and greatly appreciated!!!

REPORT EXAMPLE ATTACHED.

Future Month  

Month Closed  

Month Closed  

Month Closed  

Month Closed  

Month Closed  

Future Month  

SEP-22

SEP-21

OCT-21

OCT-20

NOV-21

NOV-20

MAY-22

Actual

Actual

Actual

Actual

Actual

Actual

Actual

$0

$0

$0

$0

$0

$0

$0

$0

$5,075

$0

$0

$0

$130

$0

 
Posted : 30/03/2022 1:49 pm
Alan Sidman
(@alansidman)
Posts: 223
Member Moderator
 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Future Month  ", type any}, {"Month Closed  ", type any}, {"Month Closed  2", type any}, {"Month Closed  3", type any}, {"Month Closed  4", type any}, {"Month Closed  5", type any}, {"Future Month  6", type any}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Changed Type2" = Table.TransformColumnTypes(#"Transposed Table",{{"Column2", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Column2", Order.Ascending}}),
#"Transposed Table1" = Table.Transpose(#"Sorted Rows"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Future Month  6", type any}, {"Month Closed  ", type any}, {"Future Month  ", type any}, {"Month Closed  3", type any}, {"Month Closed  2", type any}, {"Month Closed  5", type any}, {"Month Closed  4", type any}}),
#"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type3", [PromoteAllScalars=true]),
#"Changed Type4" = Table.TransformColumnTypes(#"Promoted Headers1",{{"10/31/2020", type any}, {"11/30/2020", type any}, {"9/30/2021", type any}, {"10/31/2021", type any}, {"11/30/2021", type any}, {"5/31/2022", type any}, {"9/30/2022", type any}})
in
#"Changed Type4"

 
Posted : 30/03/2022 4:45 pm
Share: