Log In
Register
Home
September 14, 2021
Offline
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 |
Trusted Members
October 18, 2018
Online
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"
1 Guest(s)
