Power Query - Allocate Amount (Reverse Running Total) -- simplification

[Comfy]

Hi,

I have created a proof of concept that allows me to allocate expenditure to different budgets (funding streams) based on a priority.

But the solutions feels abit clunky partly due to the final IF statement.

Does anyone have any better ideas of how to accomplish this?

Query - Source

let
    //Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZIxCsAgDEXv4uwQ00rp2muIW5dC196/xhrIUv0Gg394wkN+Si7wskbn3fFc91nuICvTMpWNRC77PssT7DbB1khUX/yxLAP6Kov4Kov4KlvOPtBdZEBdZRFdZRFdZSV2dHmiDZYd6Vp2pGtZib3v5Yk2WBbxRdtg2RoHvmgdLIv4onWw7FeH5ptf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Type = _t, Period = _t, Project = _t, Fund = _t, Amount = _t, Budget = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", Int64.Type}, {"Type", type text}, {"Project", type text}, {"Fund", Int64.Type}, {"Amount", Currency.Type}, {"Budget", Currency.Type}, {"Period", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Amount", "Budget"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> 0)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Priority", each if [Fund] = null and [Attribute] = "Amount" then 0 else if [Fund] = null then 99 else [Fund]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Period", "Project"}, {{"Data", each Table.Sort(_,{{"Period", Order.Ascending}, {"Project", Order.Ascending}, {"Priority", Order.Ascending}}), type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "WithTotal", each CreateTable([Data])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"WithTotal"}),
    #"Expanded WithTotal" = Table.ExpandTableColumn(#"Removed Other Columns", "WithTotal", {"Account", "Type", "Period", "Project", "Fund", "Attribute", "Value", "Amount", "Priority", "RunningTotal"}, {"Account", "Type", "Period", "Project", "Fund", "Attribute", "Value", "Amount", "Priority", "RunningTotal"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded WithTotal", each ([Attribute] = "Budget")),
    #"Added Custom3" = Table.AddColumn(#"Filtered Rows1", "Actual", each if [Priority] = 7 then if [RunningTotal] > 0 then [RunningTotal] else 0 else if [RunningTotal] > [Value] then [Value] else if [RunningTotal] > 0 then [RunningTotal] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Attribute", "Amount", "Priority", "RunningTotal"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Budget"}, {"Actual", "Amount"}})
in
    #"Renamed Columns"

Query - fxGroupedRunningTotal

(values as list, grouping as list) as list =>

let
    GRTList = List.Generate
    (
        ()=> [ GRT = values{0}, i = 0 ],

        each [i] < List.Count(values),

        each try if [i] = 0
            then [GRT = [GRT], i = [i] + 1]
            else [GRT = [GRT] - values{[i] + 1}, i = [i] + 1]
        otherwise [i = [i] + 1],

    each [GRT])
in
    GRTList

Query - CreateTable

(Source as table)=>

let

    List1 = Source[Period],
    List2 = Source[Project],
    Zipped = List.Zip({List1,List2}),
    ToTable = Table.FromList(Zipped, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Combined = Table.TransformColumns(ToTable, {{"Column1", each Text.Combine(_), type text}}),
   
    BufferedValues = List.Buffer(Source[Value]),
    BufferedGrouping = List.Buffer(Combined[Column1]),

    RT = Table.FromColumns(
        {
            Source[Account],
            Source[Type],
            Source[Period],
            Source[Project],        
            Source[Fund],
            Source[Attribute],
            Source[Value],
            Source[Priority],
            fxGroupedRunningTotal(BufferedValues, BufferedGrouping)
        },
        {
            "Account",
            "Type",
            "Period",
            "Project",        
            "Fund",
            "Attribute",
            "Value",
            "Priority",
            "RunningTotal"
        })


in
    RT

 

[Comfy]

Thanks for the vid Bo_Ry,

That should be enough info for me to get rid of the two functions (RunningTotal and CombineTable) and the If statement.

 

[Comfy]

I was able to rewrite without the functions in other Queries.

let
  //Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],                                                                                                                                                                                       
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZMxDsMgDEXvwhwh56coeO01omxdKnXt/Ru3gCw1sW2B8PBAT/DZtjRjuZU0pfv7+Xoc6yxTqvUkK1HaJ5tFYxFg18aWACstCmXm76YrHFJK2Tq6sxisi0aMO3sMJjJtFyllC0Ohs8O2ZHbhiG9npTV8cRaIyiwv4vFwNDTrKWtWWuuOcZoIF3UCodGIrQoE4Nj+J8JFx49bS831+jGimdDsLxNNef8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Type = _t, Period = _t, Project = _t, Fund = _t, Amount = _t, Budget = _t]), 
  ChangeType = Table.TransformColumnTypes(
    Source, 
    {
      {"Account", Int64.Type}, 
      {"Type", type text}, 
      {"Project", type text}, 
      {"Fund", Int64.Type}, 
      {"Amount", Currency.Type}, 
      {"Budget", Currency.Type}, 
      {"Period", type text}
    }
  ), 
  UnpivotCols = Table.Unpivot(ChangeType, {"Amount", "Budget"}, "Attribute", "Value"), 
  RemoveZeros = Table.SelectRows(UnpivotCols, each ([Value] <> 0)), 
  AddPriority = Table.AddColumn(
    RemoveZeros, 
    "Priority", 
    each if [Fund] = null and [Attribute] = "Amount" then 0 else if [Fund] = null then 99 else [Fund]
  ), 
  ApplySort = Table.Sort(
    AddPriority, 
    {{"Period", Order.Ascending}, {"Project", Order.Ascending}, {"Priority", Order.Ascending}}
  ), 
  AddActual = Table.AddColumn(
    ApplySort, 
    "Actual", 
    (OT) =>
      if OT[Attribute] = "Amount" then
        0
      else
        List.Max(
          {
            //Test for Fund 7
            if OT[Priority]
              = List.Max(
                Table.SelectRows(
                  ApplySort, 
                  (IT) => IT[Period] = OT[Period] and IT[Project] = OT[Project] and IT[Attribute] = "Budget"
                )[Priority]
              )
            then
            //For Fund 7 the Running Total is the Sum of Amount per Aggregation Group less the sum of Budget per Aggregation Group
              List.Sum(
                Table.SelectRows(
                  ApplySort, 
                  (IT) => IT[Period] = OT[Period] and IT[Project] = OT[Project] and IT[Attribute] = "Amount"
                )[Value]
              )
                - List.Sum(
                  Table.SelectRows(
                    ApplySort, 
                    (IT) => IT[Period] = OT[Period] and IT[Project] = OT[Project] and IT[Attribute] = "Budget"
                  )[Value]
                )
            //Set the Running Total for all other funds to zero as tested below
            else
              0,
            //For all other funs we want the lower of the RunningTotal or the Budget 
            List.Min(
              {
                OT[Value], 
                List.Sum(
                  Table.SelectRows(
                    ApplySort, 
                    (IT) => IT[Period] = OT[Period] and IT[Project] = OT[Project] and IT[Attribute] = "Budget" and IT[Priority] <= OT[Priority]
                  )[Value]
                )
              }
            ),
            //Finally, include zero in Max statment to ensure now negatives are returned 
            0
          }
        )
  ),
    #"Filtered Rows" = Table.SelectRows(AddActual, each ([Attribute] = "Budget")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Priority"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Budget"}})
in
  #"Renamed Columns"

It's probably harder to understand now...... which is arguably worse.

But atleast I have two different options to test with real world data.

 

[Comfy]

Sorry ran out of time to edit. I pasted the wrong version of the code.

let
  //Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],                                                                                                                                                                                      
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZMxDsMgDEXvwhwh56coeO01omxdKnXt/Ru3gCw1sW2B8PBAT/DZtjRjuZU0pfv7+Xoc6yxTqvUkK1HaJ5tFYxFg18aWACstCmXm76YrHFJK2Tq6sxisi0aMO3sMJjJtFyllC0Ohs8O2ZHbhiG9npTV8cRaIyiwv4vFwNDTrKWtWWuuOcZoIF3UCodGIrQoE4Nj+J8JFx49bS831+jGimdDsLxNNef8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Type = _t, Period = _t, Project = _t, Fund = _t, Amount = _t, Budget = _t]),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {
      {"Account", Int64.Type},
      {"Type", type text},
      {"Project", type text},
      {"Fund", Int64.Type},
      {"Amount", Currency.Type},
      {"Budget", Currency.Type},
      {"Period", type text}
    }
  ),
  UnpivotCols = Table.Unpivot(ChangeType, {"Amount", "Budget"}, "Attribute", "Value"),
  RemoveZeros = Table.SelectRows(UnpivotCols, each ([Value] <> 0)),
  AddPriority = Table.AddColumn(
    RemoveZeros,
    "Priority",
    each if [Fund] = null and [Attribute] = "Amount" then 0 else if [Fund] = null then 99 else [Fund]
  ),
  ApplySort = Table.Sort(
    AddPriority,
    {{"Period", Order.Ascending}, {"Project", Order.Ascending}, {"Priority", Order.Ascending}}
  ),
  AddActual = Table.AddColumn(
    ApplySort,
    "Actual",
    (OT) =>
      if OT[Attribute] = "Amount" then
        0
      else
        List.Max(
          {
            //Test for Fund 7
            if OT[Priority]
              = List.Max(
                Table.SelectRows(
                  ApplySort,
                  (IT) => IT[Period] = OT[Period] and IT[Project] = OT[Project] and IT[Attribute] = "Budget"
                )[Priority]
              )
            then
            //For Fund 7 the Running Total is the Sum of Amount per Aggregation Group less the sum of Budget per Aggregation Group
              List.Sum( //Sum of Amount
                Table.SelectRows(
                  ApplySort,
                  (IT) => IT[Period] = OT[Period] and IT[Project] = OT[Project] and IT[Attribute] = "Amount"
                )[Value]
              )
                - List.Sum( //Sum of Budget
                  Table.SelectRows(
                    ApplySort,
                    (IT) => IT[Period] = OT[Period] and IT[Project] = OT[Project] and IT[Attribute] = "Budget"
                  )[Value]
                )
            //Set the Running Total for all other funds to zero as tested below
            else
              0,
            //For all other funs we want the lower of the RunningTotal or the Budget
            List.Min(
              {
                OT[Value], //Budget
                List.Sum( //Sum of Amount
                Table.SelectRows(
                  ApplySort,
                  (IT) => IT[Period] = OT[Period] and IT[Project] = OT[Project] and IT[Attribute] = "Amount"
                )[Value]
              )
              - List.Sum( // Less Sum of Budget
                  Table.SelectRows(
                    ApplySort,
                    (IT) => IT[Period] = OT[Period] and IT[Project] = OT[Project] and IT[Attribute] = "Budget" and IT[Priority] < OT[Priority]
                  )[Value]
                )
              }
            ),
            //Finally, include zero in Max statment to ensure now negatives are returned
            0
          }
        )
  ),
    #"Filtered Rows" = Table.SelectRows(AddActual, each ([Attribute] = "Budget")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Priority"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Budget"}})
in
  #"Renamed Columns"