Forum Discussion

chaitanya1's avatar
chaitanya1
Icon for Helper I rankHelper I
1 year ago
Solved

Custom Column for Cummulative TR in power query

let // Load the Serviceable Feeder table ServiceableFeeders = ServiceableFeeder, // Load the Master Address List (2) table with MonthsSinceGoLive MonthsTable = QuartersSinceLiveSeries, // Add a ...
data protection
Interesting Links
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi chaitanya1 ,

     

    Please try like:

    Cumulative TR = 
VAR __curr_index = 'Table'[Index]
VAR __result = CALCULATE(SUM('Table'[TR]), FILTER(ALL('Table'), 'Table'[Index] <= __curr_index))
RETURN
    __result

    or:

    Cumulative TR = 
VAR __curr_index = 'Table'[Index]
VAR __result = CALCULATE(SUM('Table'[TR]),  'Table'[Index] <= __curr_index, ALLEXCEPT('Table','Table'[Index]))
RETURN
    __result

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

chaitanya1 To add a cumulative TR column in your Power Query M code, you can use a combination of List.Generate and List.Accumulate functions to calculate the cumulative sum. Here is how you can modify your existing code to include a cumulative TR column:

 

m
let
// Load the Serviceable Feeder table
ServiceableFeeders = ServiceableFeeder,
// Load the Master Address List (2) table with MonthsSinceGoLive
MonthsTable = QuartersSinceLiveSeries,
// Add a dummy key column with a constant value to both tables
AddKeyServiceableFeeders = Table.AddColumn(ServiceableFeeders, "Key", each 1),
AddKeyMonthsTable = Table.AddColumn(MonthsTable, "Key", each 1),

// Perform a Cartesian product by merging on the Key column
MergedTables = Table.Join(AddKeyServiceableFeeders, "Key", AddKeyMonthsTable, "Key", JoinKind.Inner),

// Remove the Key column, as it's no longer needed
RemoveKeyColumn = Table.RemoveColumns(MergedTables, {"Key"}),
#"Merged Queries2" = Table.NestedJoin(RemoveKeyColumn, {"City"}, #"Serviceable Max Quarter", {"City"}, "Serviceable Max Months", JoinKind.LeftOuter),
#"Expanded Serviceable Max Months" = Table.ExpandTableColumn(#"Merged Queries2", "Serviceable Max Months", {"Max Quarter"}, {"Max Quarter"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Serviceable Max Months", each ([Max Quarter] <> null)),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows",{{"Max Quarter", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type3", "Filter", each if [QuarterSinceGoLive M] > [Max Quarter] then 0 else 1),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"QuarterSinceGoLive M", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type2", each ([Filter] = 1)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows1", {"Serviceable Feeder", "MonthsSinceGoLive M", "Distribution Point Type", "Residential/ Business"}, #"Master Address List", {"Serviceable Feeder", "MonthsSinceGoLive M", "Distribution Point Type", "Residential/ Business"}, "Master Address List", JoinKind.LeftOuter),
#"Aggregated Master Address List" = Table.AggregateTableColumn(#"Merged Queries", "Master Address List", {{"Active Customers", List.Sum, "Sum of Active Customers"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Aggregated Master Address List",{{"Sum of Active Customers", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Sum of Active Customers"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"QuarterSinceGoLive M", Order.Ascending}}),
#"Merged Queries1" = Table.NestedJoin(#"Sorted Rows", {"Serviceable Feeder", "Distribution Point Type", "Residential/ Business"}, #"Master Address Group - Cohort", {"Serviceable Feeder", "Distribution Point Type", "Residential/ Business"}, "Master Address List (3)", JoinKind.LeftOuter),
#"Expanded Master Address List (3)" = Table.ExpandTableColumn(#"Merged Queries1", "Master Address List (3)", {"Serviceable Units", "Active Customers", "Premises Ready for Service", "Serviceable Pending ROE", "Serviceable Approved ROE"}, {"Serviceable Units", "Active Customers", "Premises Ready for Service", "Serviceable Pending ROE", "Serviceable Approved ROE"}),
#"Added Custom" = Table.AddColumn(#"Expanded Master Address List (3)", "TR", each [Sum of Active Customers]/[Serviceable Units]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"TR", Percentage.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Type", each "Quarter"),
#"Replaced Value1" = Table.ReplaceValue(#"Added Custom1","Duplex Commercial","Duplex commercial",Replacer.ReplaceText,{"Distribution Point Type"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Duplex Residential","Duplex residential",Replacer.ReplaceText,{"Distribution Point Type"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","DUPLEX RESIDENTIAL","Duplex residential",Replacer.ReplaceText,{"Distribution Point Type"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Single Commercial","Single commercial",Replacer.ReplaceText,{"Distribution Point Type"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Vacant Lot","Vacant lot",Replacer.ReplaceText,{"Distribution Point Type"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Other","other",Replacer.ReplaceText,{"Distribution Point Type"}),

// Add cumulative TR column
#"Added Index" = Table.AddIndexColumn(#"Replaced Value6", "Index", 1, 1, Int64.Type),
#"Added Cumulative TR" = Table.AddColumn(#"Added Index", "Cumulative TR", each List.Sum(List.FirstN(#"Added Index"[TR], [Index]))),
#"Removed Index" = Table.RemoveColumns(#"Added Cumulative TR", {"Index"})
in
#"Removed Index"