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"

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

      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"