powerquery - See maximum value in a nested table - Stack Overflow

admin管理员组

文章数量:1287649

I have a problem where my total percentages for each site must reconcile to a total of 1. So if the total percentage adds up to 1.01, then I need to reduce the row with the largest percentage by 0.01. If the total percentage adds up to 0.99, then I need to increase the row with the largest percentage by 0.01. If the Total Percentage adds up to 1, then default to the original percentage.

I have tried the following code, but it applies any newly calculated percentage to all rows, not just the row with the maximum value.

let maximum=List.Max([Table][Percentage]) in 
Table.AddColumn([Table],"New Percentage", each if [Total Percentage] < 1 then maximum + 0.01 else if [Total Percentage] > 1 then maximum - 0.01 else [Percentage])  

I have a problem where my total percentages for each site must reconcile to a total of 1. So if the total percentage adds up to 1.01, then I need to reduce the row with the largest percentage by 0.01. If the total percentage adds up to 0.99, then I need to increase the row with the largest percentage by 0.01. If the Total Percentage adds up to 1, then default to the original percentage.

I have tried the following code, but it applies any newly calculated percentage to all rows, not just the row with the maximum value.

let maximum=List.Max([Table][Percentage]) in 
Table.AddColumn([Table],"New Percentage", each if [Total Percentage] < 1 then maximum + 0.01 else if [Total Percentage] > 1 then maximum - 0.01 else [Percentage])  

• powerquery

Share Improve this question Follow edited Feb 28 at 10:05 agilgur5 8391515 silver badges3535 bronze badges asked Feb 23 at 14:41 DullesTowerDullesTower 3744 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

This works. Idea is to sort by group, Add an index by group to identify the max row and than add the new percentage only to the max row:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUMTBUitWJVjICcgx0TJDYxkhsIzDbGMy2VIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Website = _t, Percentage = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Percentage", type number}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Website", Order.Ascending}, {"Percentage", Order.Descending}}),
        #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Website"}, {{"Sum", each List.Sum([Percentage]), type nullable number}, {"All", each _, type table [Website=nullable text, Percentage=nullable number]}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddIndex", each Table.AddIndexColumn([All],"Index",1)),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Sum", "AddIndex"}),
        #"Expanded AddIndex" = Table.ExpandTableColumn(#"Removed Other Columns", "AddIndex", {"Website", "Percentage", "Index"}, {"Website", "Percentage", "Index"}),
        #"Added Custom1" = Table.AddColumn(#"Expanded AddIndex", "New percentage", each let
                    Diff = 1 - [Sum],
                    result = if [Index] = 1 then [Percentage]+ Diff else [Percentage]
                in
                    result),
        #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Website", "New percentage"})
    in
        #"Removed Other Columns1"

Adjusting for the difference is complicated in order to restrict it to the row with the highest value.

In the code below we

• Group by site
• within each group we
  • Add an index column to keep the original sort order
  • Sort by Percentage (descending)
  • Alter only the first row
  • Sort back to the original order
  • Add back the original percentages

Paste the code below into the Advanced Editor, and change the Table Name in the Source line to your actual table name

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Site", type text}, {"Percentage", Percentage.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Site"}, {
        {"Total Percentage", each List.Sum([Percentage]), type number},
        {"New Percentage", (t)=>
            let 
                maxP=List.Max(t[Percentage]),
                diff = 1-List.Sum(t[Percentage]),
                adjust = 
                    [   a = Table.AddIndexColumn(t,"Index"), 
                        b = Table.Sort(a,{"Percentage",Order.Descending}),
                        c = Record.TransformFields(b{0},{"Percentage", each _ + diff}),
                        d = Table.FromRecords({c} & Table.ToRecords(Table.Skip(b))),
                        e = Table.Sort(d,{"Index", Order.Ascending}),
                        f = Table.RemoveColumns(e,"Index"),
                        g = Table.RenameColumns(f,{"Percentage","New Percentage"}),
                        h = Table.FromColumns(
                                {Table.Column(t,"Percentage")}
                                &Table.ToColumns(g),
                                {"Percentage", "Site", "New Percentage"}
                        ) ][h]
                
                
          
            in adjust, type table[Percentage=Percentage.Type,Site=text, New Percentage = Percentage.Type]
            
            
                    


    }}),
    #"Expanded New Percentage" = Table.ExpandTableColumn(#"Grouped Rows", "New Percentage", {"Percentage", "New Percentage"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded New Percentage",{"Site", "Percentage", "Total Percentage", "New Percentage"})
in
    #"Reordered Columns"

Another powerquery method

Add index. Group. Sort by percentage. Replace the first percentage with 1-[sum]. Expand. Resort

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Site"}, {{"data", each     
    let x=    Table.Sort(_,{{"Percentage", Order.Descending}})
    in Table.ReplaceValue(x,x{0}[Percentage],1-List.Sum(List.RemoveFirstN(x[Percentage],1)),Replacer.ReplaceValue,{"Percentage"})
, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Percentage", "Index"}, {"Percentage", "Index"}),
#"Sorted Rows" = Table.Sort(#"Expanded data",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in #"Removed Columns"

本文标签： powerquerySee maximum value in a nested tableStack Overflow