Power Query sort ascending or descending based on a parameter

%3CLINGO-SUB%20id%3D%22lingo-sub-1514233%22%20slang%3D%22en-US%22%3EPower%20Query%20sort%20ascending%20or%20descending%20based%20on%20a%20parameter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514233%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EWhat%20do%20i%20want%3A%3C%2FSTRONG%3E%3CBR%20%2F%3ETo%20sort%20based%20on%20a%20parameter%20in%20power%20query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EWhy%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20am%20grouping%20records%2C%20the%20top%20record%20in%20each%20duplicate%20determines%20which%20value%20will%20be%20used%3B%3C%2FP%3E%3CP%3Ein%20%3CEM%3Eascending%3C%2FEM%3E%20it%20will%20be%20the%20%3CEM%3Elowest%3C%2FEM%3E%20value%2C%20in%20%3CEM%3Edescending%3C%2FEM%3E%20it%20will%20be%20the%20%3CEM%3Ehighest%3C%2FEM%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20to%20use%20both%20a%20custom%20function%20and%20directly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%20%20%20%20%23%22Rijen%20gesorteerd%22%20%3D%20%23!%22Table.Sort(%23%22%22Lege%20rijen%20verwijderd%22%22%2C%7B%7B%22%22Craft%22%22%2C%20Order.Ascending%7D%2C%20%7B%22%22Cost%22%22%2C%20order.%20%26amp%3B%20Sort_AscDesc%7D%2C%20%7B%22%22Seed.Lvl%22%22%2C%20Order.Descending%7D%7D)%22%2C%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Sort_AscDesc%20is%20the%20parameter%20i'm%20using.%3C%2FP%3E%3CP%3EI've%20tried%20to%20do%20the%20full%20Order.Ascending%20in%20the%20parameter%2C%20tried%20changing%20the%20type%2C%20but%20so%20far%20all%20is%20rejected%3B%20with%20and%20without%20'%26amp%3B'%20and%20such%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20reckon%20maybe%20i%20can%20created%20a%20custom%20function%20for%20it%2C%20but%20i%20rather%20keep%20it%20as%20simple%20as%20possible%3F%20I'm%20using%20parameters%20in%20the%20advanced%20editor%20before%20without%20issues%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated%2C%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1514233%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516621%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20sort%20ascending%20or%20descending%20based%20on%20a%20parameter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723876%22%20target%3D%22_blank%22%3E%40CareLess%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Power%20Query%20global%20constants%20are%20equal%3C%2FP%3E%0A%3CP%3EOrder.Descending%20to%201%3C%2FP%3E%0A%3CP%3EOrder.Ascendinng%20to%200%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThus%20with%20parameter%20that%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3ESort_AscDesc%20%3D%200%2C%0A%23%22Rijen%20gesorteerd%22%20%3D%20%23!%22Table.Sort(%23%22%22Lege%20rijen%20verwijderd%22%22%2C%0A%20%20%20%7B%7B%22%22Craft%22%22%2C%20Order.Ascending%7D%2C%0A%20%20%20%20%7B%22%22Cost%22%22%2C%20Sort_AscDesc%7D%2C%0A%20%20%20%20%7B%22%22Seed.Lvl%22%22%2C%20Order.Descending%7D%7D%0A)%22%2C%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

1 Reply

@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}}
)",