Forum Discussion

PhilippeMuniesa's avatar
PhilippeMuniesa
Icon for Resolver I rankResolver I
6 years ago
Solved

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

 

Need Help
  • PhilippeMuniesa's avatar
    PhilippeMuniesa
    6 years ago

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

     

    Cordialement

     

    Philippe

     

     

4 Replies

  • PhilippeMuniesa's avatar
    PhilippeMuniesa
    Icon for Resolver I rankResolver I
    6 years ago

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

     

    Thank you

     

    Philippe

    • CNENFRNL's avatar
      CNENFRNL
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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"

      • PhilippeMuniesa's avatar
        PhilippeMuniesa
        Icon for Resolver I rankResolver I
        6 years ago

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

         

        Cordialement

         

        Philippe

         

         

    • PhilippeMuniesa's avatar
      PhilippeMuniesa
      Icon for Resolver I rankResolver I
      6 years ago

      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