Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"
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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@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"
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"
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
@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"
Hello
Thank you for sharing your solution!
Best Regards
Maggie
User | Count |
---|---|
98 | |
90 | |
84 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |