Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |