Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
8 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 Days 10 Days 20 Consumption 30 Consumption 40 Cost 50 Cost 60  ...
Need Help
  • smpa01's avatar
    smpa01
    8 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"

     

LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
8 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
    Icon for Community Champion rankCommunity Champion
    8 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
      Icon for Community Champion rankCommunity Champion
      8 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"

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hello

        Thank you for sharing your solution!

         

        Best Regards

        Maggie