Forum Discussion

jaipal's avatar
jaipal
Resolver III
4 years ago
Solved

Power Query Running Totals

Hello Community, I am facing an issue while runnign totals in Power Query.   When I run totals for revenue and cost the output is correct:                           But when I merge th...
  • wdx223_Daniel's avatar
    4 years ago

    let
    Source = Fact,
    #"Filtered Rows" = Table.SelectRows(Source, each ([ProjectKey] = 430) and ([FiscalYear] = 2021)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"DateKey", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ProjectKey", "FiscalYear"}, {"Count", each _,}),
    // Function to calculate running totals
    RunFunction = (RunTable as table) as table=>
    let
    #"Added Index" = Table.AddIndexColumn(RunTable, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "n", each {List.Sum(List.Range(#"Added Index" [Revenue],0,[Index])),List.Sum(List.Range(#"Added Index" [CostAmount],0,[Index]))}),

    Custom1 = Table.SplitColumn(#"Added Custom","n",each _,{"CummulativeRevenue","CummulativeCost"})
    in
    Custom1,
    // call the function
    RunTotals= Table.TransformColumns(#"Grouped Rows", {"Count", each RunFunction(_)}),
    #"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeRevenue","CummulativeCost"}, {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeRevenue","CummulativeCost"})

    in

    #"Expanded Count"