Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm trying to use Table.ReorderColumns in PQ in Excel 2016 using the following M code (from here which was a solution for Power BI)
=Table.ReorderColumns( NameOfPreviousStep, List.Order( Table.ColumnNames( NameOfPreviousStep ), Order.Ascending )
The issue is that the function List.Order does not exist in PQ in Excel, instead I have to specifically specifiy the order of the column as per
= Table.ReorderColumns(NameOfPreviousStep,{"ProjectName", "2017 01", "2017 02"})
Does anyone know how I can automatically order the column names in an Ascending way in Excel?
Thanks, Ben.
Hi,
Please share a dataset and also show the exected result.
Hi,
Here's an example dataset prior to the column sort...
Customer2018 12 2018 10 2018 01
a | 10 | 13 | 6 |
b | 10 | 14 | 4 |
c | 20 | 15 | 9 |
Here's an example of the dataset after the column sort...
2018 01 2018 10 2018 12 Customer
6 | 13 | 10 | a |
4 | 14 | 10 | b |
9 | 15 | 20 | c |
Thanks, Ben.
Hi,
I can get your desired result in the final Pivot Table that i create. Will that do?
Hi Ashish,
I can manually reorder them, it's no real issue, but I what I want to do is automatically reorder them.
I understand that. All i am saying is that i can dynamicaly sort those columns in the Pivot table that gets created via the Data Model instead of sorting it in POwer Query via the M code. Will that be OK with you?
Hi, thanks for the offer, but I know how to do that.
Hi Ben
I was struggling a bit with this also. It seems the M function to use is List.Sort. Perhaps it used to be called List.Order and has been renamed.
This code works for me in Power Query in Excel 2016:
Table.ReorderColumns( Source, List.Sort( Table.ColumnNames( Source ), Order.Ascending))