Forum Discussion

smpa01's avatar
smpa01
Community Champion
7 years ago
Solved

Power Query Custom Sort

Hi,

 

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

 

E.g. this is the raw data

Column Value
Days10
Days20
Consumption30
Consumption40
Cost50
Cost60

 

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

 

Column Value
Days10
Consumption30
Cost50
Days20
Consumption40
Cost60

 

Is it possible to achieve in Power Query?

 

Thanks

 

 

Need Help
  • smpa01's avatar
    smpa01
    7 years ago

    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"

     

6 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    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's avatar
      smpa01
      Community Champion
      7 years ago

      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's avatar
        smpa01
        Community Champion
        7 years ago

        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"