Reverse Colums

H

Heyjoe

New member
Joined
Jan 3, 2019
Messages
59
Reaction score
0
Points
0
Location
USA
Excel Version(s)
2019
Hello Fellow Excel Users

I set up a spreadsheet like this:

Date2018201720162015
Sales200229150100

I don't know why I did this. Now the dates seem backwards to me.

I would like to reverse the columns so that 2015 is on the left and 2018 is on the right.

Is there a command in excel to do this?
 
Load your SS into Power Query/Get and Transform then apply the following Mcode

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"2018", Int64.Type}, {"2017", Int64.Type}, {"2016", Int64.Type}, {"2015", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Date", "2015", "2016", "2017", "2018"})
in
    #"Reordered Columns"
 
Alan's suggestion will work, but might fail if the column headings subsequently change. This might be more robust:

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"
 
Thank you both so much. I am not familiar with power queries. Would it work if the spreadsheet has a lot of rows in it?
 
Yes, it will work with lots of rows - try it!

Alan - the more I use PQ, the more I realise that there are, inevitably, pitfalls. Anything specifically defined in a stage of the M code (especially column names) can cause the query to break if things change. Gil Raviv's book has made me think a lot more about this when building queries from scratch.
 
Late, I know, but there's a horizontal sort built in.
Before:
2019-03-27_225131.jpg
after:
2019-03-27_225254.jpg
 
Thanks everyone. The simple horizontal sort is what I was looking for.
 
You must log in or register to reply here.
Back
Top