Power Query Custom Sort

[smpa01]

Hi,

Is it possible to do a custom sort in Power Query.

E.g. this is the raw data

Column	Value
Days	10
Days	20
Consumption	30
Consumption	40
Cost	50
Cost	60

I want the "Column" column to be sorted as Days, Consumption, Cost, like below.

Column	Value
Days	10
Consumption	30
Cost	50
Days	20
Consumption	40
Cost	60

Is it possible to achieve in Power Query?

Thanks

[LivioLanzo]

Hi @smpa01

You could create another table which contains the orders in which you want the column "Column" to be displayed, something like:

Column | SortValue

Days | 1

Consumption | 2

Cost | 3

then perform a merge between your Original table and this table and import the SortValue column. Afterwards sort your Original table by Value and then by Column

[smpa01]

@LivioLanzothanks for the reply. Can you please take a look as I could not get the code to work what I was looking for

Table 0

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Power-Query-Custom-Sort/m-p/523096")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Column"},#"Table 1",{"Column"},"Table 1",JoinKind.LeftOuter),
    #"Expanded Table 1" = Table.ExpandTableColumn(#"Merged Queries", "Table 1", {"Index"}, {"Index"})
in
    #"Expanded Table 1"

Table 1

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Power-Query-Custom-Sort/m-p/523096")),
    Data1 = Source{1}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column", type text}, {"Value", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1)
in
    #"Added Index"

[smpa01 · Accepted solution]

I solved this problem in following two ways and I would go for Method 2

Method 1

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Power-Query-Custom-Sort/m-p/523096")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Column"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AD], "Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AD"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value", "Index"}, {"Value", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

Method 2

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Power-Query-Custom-Sort/m-p/523096")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Column"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AD], "Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AD"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value", "Index"}, {"Value", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Index", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Custom1" = Table.AddColumn(#"Removed Duplicates", "Custom", each {"Days", "Consumption", "Cost"}),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom1", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index", "Custom"},#"Expanded Custom",{"Index", "Column"},"Expanded Custom1",JoinKind.LeftOuter),
    #"Expanded Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom1", {"Value"}, {"Value"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Expanded Custom1",{{"Index.1", Order.Ascending}})
in
    #"Sorted Rows1"

[Anonymous]

Hello @smpa01 ,

Can you please elaborate a little bit your answer? Just adding a comment per step int he formula would be useful.

Appreciate your help. 

Sona

[smpa01]

@Anonymoussorry for the delay in replying..been busy earlier. I put the comments for you to udnderstand for Method 2.

I worked backwards to solve this problem.

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Power-Query-Custom-Sort/m-p/523096")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    // Grouped value by column//
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Column"}, {{"AD", each _, type table}}),
    // Adding Index to the each value by column e.g. Days-10, Days-20; Consumpton-30, Consumption-50//
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AD], "Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AD"}),
    // Expanding the  value table taht has index attached to it//
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value", "Index"}, {"Value", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Index", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    // Hardcoding the Column value against each sorted index//
    #"Added Custom1" = Table.AddColumn(#"Removed Duplicates", "Custom", each {"Days", "Consumption", "Cost"}),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    // Index added for sorting later//
    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom1", "Index.1", 1, 1),
    // In this step the table generated in #Added Index step is joined to the table generated in #Expanded Custom step
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index", "Custom"},#"Expanded Custom",{"Index", "Column"},"Expanded Custom1",JoinKind.LeftOuter),
    #"Expanded Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom1", {"Value"}, {"Value"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Expanded Custom1",{{"Index.1", Order.Ascending}})
in
    #"Sorted Rows1"

[v-juanli-msft]

Hello

Thank you for sharing your solution!

Best Regards

Maggie