Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |