Forum Discussion

CareLess's avatar
CareLess
Copper Contributor
Jul 10, 2020

Power Query sort ascending or descending based on a parameter

Hi all,

 

What do i want:
To sort based on a parameter in power query

 

Why

I am grouping records, the top record in each duplicate determines which value will be used;

in ascending it will be the lowest value, in descending it will be the highest.

 

I've tried to use both a custom parameter function and a direct parameter defined in power query.

 

 

 

 

 

    #"Rijen gesorteerd" = #!"Table.Sort(#""Lege rijen verwijderd"",{{""Craft"", Order.Ascending}, {""Cost"", order. & Sort_AscDesc}, {""Seed.Lvl"", Order.Descending}})",

 

 

 

 

The Sort_AscDesc is the parameter i'm using.

I've tried to do the full Order.Ascending in the parameter, tried changing the type, but so far all is rejected; with and without '&' and such as well.

 

I reckon maybe i can created a custom function for it, but i rather keep it as simple as possible? I'm using parameters in the advanced editor before without issues?

 

 

Any help is greatly appreciated,

Thanks!

BI & Data Analysis

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 11, 2020

    CareLess 

    In Power Query global constants are equal

    Order.Descending to 1

    Order.Ascendinng to 0

     

    Thus with parameter that could be like

    Sort_AscDesc = 0,
#"Rijen gesorteerd" = #!"Table.Sort(#""Lege rijen verwijderd"",
   {{""Craft"", Order.Ascending},
    {""Cost"", Sort_AscDesc},
    {""Seed.Lvl"", Order.Descending}}
)",

     

Resources