none
Reorder Columns selectively RRS feed

 > 

  • General discussion

  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Hello, 

    I'm looking to reorder columns in Power Query but I want the column order to be ascending and other select columns to be at the left most side of the table. 

    The table will have the Columns of : "Item Name", "Units", "Cost", "ratio", "source", (the rest of the column order will be ascending).

    In terms of M-Functions advanced editor it might look like this. 

    "Reordered Columns" = Table.ReorderColumns("Prior Step",{"Item Name", "Units", "Cost", "ratio", "source"} & List.Sort(Table.ColumnNames("Prior Step"),Order.Ascending))

    but the above does not work. 




    • Edited by SSibbett Thursday, October 24, 2019 10:52 PM 4th time is a charm
    Thursday, October 24, 2019 10:40 PM
    |

All replies

  • Discussion
    Sign in to vote
    1
    Sign in to vote

    Hi

    With the bolded part in List.Sort(Table.ColumnNames("Prior Step"),Order.Ascending) your sort ALL your column names, inc. those you want to keep on the left (Item name, Units...source)

    So you should exclude them from the List.Sort. One way:

        StepBefore_LeftColumns = ...

    LeftColumns = {"item Name", "units", "cost", "ratio", "source"},
    ReorderList = LeftColumns &
                  List.Sort(List.Difference(Table.ColumnNames(<StepBefore_LeftColumns>),LeftColumns),Order.Ascending),
    Reorder = Table.ReorderColumns(<StepBefore_LeftColumns>, ReorderList)



    • Edited by Lz._ Friday, October 25, 2019 10:46 AM
    Thursday, October 24, 2019 11:26 PM
    |
  • Discussion
    Sign in to vote
    0
    Sign in to vote
    Thank you, this solution works :). The part that was confusing was the fact that <PreviousStep> was the one prior to the LeftColumns but once I realized that it worked great. 
    Friday, October 25, 2019 12:56 AM
    |
  • Discussion
    Sign in to vote
    0
    Sign in to vote
    Thank you, this solution works :). The part that was confusing was the fact that <PreviousStep> was the one prior to the LeftColumns 
    You're right. Updated the proposal
    Friday, October 25, 2019 10:48 AM
    |
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Hi

    As you seem to be around these days could you mark that thread as answered please? Thanks

    Wednesday, November 20, 2019 12:20 PM
    |