Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PhilippeMuniesa
Resolver I
Resolver I

columns select from X to the last

hello champions.

 

I'm a beginner, and French. A lot of handicaps, ...

 

then. I built a table with powerQuery and I would like to sort the columns of this table in ascending order. e.g. my original table Date / Piece / designation - 7071 - 6035 - 708 - 401 etc.....

and I would like to get

Date / Piece / Designation / 401 - 6035 - 7071 - 708, etc...

 

I got there with 3 steps

1) Unpivot other columns tthan (Date/Piece/Designation)

2) sort on the attribute column

3) Pivot the column attribute with value "value". 

 

it works well but I would like to progress if possible with a more elegant and perhaps more effective method. 

Thank you 

 

Philippe

 

1 ACCEPTED SOLUTION

Merci, je vais essayer et je te tiens au courant.

 

Cordialement

 

Philippe

 

 

View solution in original post

4 REPLIES 4
PhilippeMuniesa
Resolver I
Resolver I

Sorry, the title of my post is not the right one, it's about sorting the columns of a table

 

Thank you

 

Philippe

Super,

 

Based on your solution, I wrote the following function with two parameters.

This allows you to sort columns from a table from a specific column (Y-parameter).

so it's easy to reuse on other tables in an other context.

 

 

let
Source = (X,Y) => let
// X est la table à trier, Y est la colonne de départ du tri
Tri_Les_Colonnes = Table.ReorderColumns(X,
let
Headers = Table.ColumnNames(X),
List_Noms_Colonnes = List.FirstN(Headers, Y),
List_Noms_Colonnes_Triées = List.Sort(List.Skip(Headers, Y), Order.Descending)
in
List_Noms_Colonnes & List_Noms_Colonnes_Triées
)
in
Tri_Les_Colonnes
in
Source

 

A big thank you to you that allows me to progress a little and to advance on my projects.

 

Philippe Muniesa

 

 

Salut, @PhilippeMuniesa , puisque tout ce que tu voulais est de trier les colonnes, je te propose une méthode comme ça,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1NNA1MFTSUXIE4gggNgJiQwMgYQzEJkqxOkjqQHJOQBwJxJZAbArFhqjKQDqdgTgKiC2gRpqBlcbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Piece = _t, Designation = _t, #"7071" = _t, #"6035" = _t, #"708" = _t, #"401" = _t]),
    
    #"Reordered Columns" = Table.ReorderColumns(Source,
        let
            Headers = Table.ColumnNames(Source),
            #"Fixed Headers" = List.FirstN(Headers, 3),
            #"Sorted Headers" = List.Sort(List.Skip(Headers, 3), Order.Ascending)
        in
            #"Fixed Headers" & #"Sorted Headers"
    )
in
    #"Reordered Columns"

Screenshot 2020-10-29 213729.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Merci, je vais essayer et je te tiens au courant.

 

Cordialement

 

Philippe

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors