Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 those two Queries, I am getting wrong values like this:
Here is my Code:
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 _, type table [DateKey=number, MonthName=text, FiscalYear=nullable number, ProjectKey=nullable number, CostAmount=nullable number, Revenue=nullable number, BudgetMonthlyRevenue=nullable number, BudgetMonthlyCost=nullable number, BudgetYTDRevenue=nullable number, BudgetYTDCost=nullable number]}}),
// 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", "CummulativeRevenue", each List.Sum(List.Range(#"Added Index" [Revenue],0,[Index])))
in
#"Added Custom",
// call the function
RunTotals= Table.TransformColumns(#"Grouped Rows", {"Count", each RunFunction(_)}),
#"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeRevenue"}, {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeRevenue"}),
// Function to calculate running totals
RunFunction2 = (RunTable as table) as table=>
let
#"Added Index" = Table.AddIndexColumn(RunTable, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "CummulativeCost", each List.Sum(List.Range(#"Added Index" [CostAmount],0,[Index])))
in
#"Added Custom",
// call the function
RunTotals2 = Table.TransformColumns(#"Grouped Rows", {"Count", each RunFunction2(_)}),
#"Expanded Count1" = Table.ExpandTableColumn(RunTotals2, "Count", {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeCost"}, {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeCost"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Count1", {"ProjectKey", "DateKey","FiscalYear"}, #"Expanded Count", {"ProjectKey", "DateKey","FiscalYear"},"Expanded Count", JoinKind.LeftOuter),
#"Expanded Expanded Count" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Count", {"CummulativeRevenue"}, {"CummulativeRevenue"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Expanded Count",{"DateKey", "MonthName", "FiscalYear", "ProjectKey", "CummulativeCost", "CummulativeRevenue", "BudgetYTDCost", "BudgetYTDRevenue"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"CummulativeCost", "Cost"}, {"CummulativeRevenue", "Revenue"}, {"BudgetYTDCost", "BudgetCost"}, {"BudgetYTDRevenue", "BudgetRevenue"}}),
#"Sorted Rows1" = Table.Sort(#"Renamed Columns",{{"DateKey", Order.Ascending}})
in
#"Sorted Rows1"
Is the problem with Index or Merge queries?
Hope someone can help me with this..
Thanks
Solved! Go to Solution.
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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.