Forum Discussion

Helper 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 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"})
in
#"Replaced Value6"

this is my all table for Cohort Analysis M Cummulative advanced editor

i have to add custome column cummulative TR, in report view i have matrix table if i add TR column added in values it is coming each month each customer indvidual percentage so for that i have to cummulative the TR column if i add that column in matrix table values it should come like from 0 to 10 months 0+1 = 1st month....4th month + 5th month = 5th month.....9th month + 10th month = 10th month please help give me m query i have 2.5 million row huge table please give faster m query so i can use dynamically further do also

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

7 Replies

bhanu_gautam
Super 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"
chaitanya1
Helper I
1 year ago
bhanu_gautam Thank you so much
chaitanya1
Helper I
1 year ago
bhanu_gautam i have 2.5 million rows in table it is taking hours by using this List.Sum(List.FirstN) reading and loading every row there is any alternative to that please i have try same method before.
- danextian
  Super User
  1 year ago
  Hi bhanu_gautam
  
  Why create this using M? While it is feasible, generating a cumulative value column in a table with 2.5 million rows will be computationally expensive and slow if it doesn't time out altogether. I've attempted this with rows generated entirely within the query editor, and that one even timed out. Handling external data with additional ETL processes applied would only make it even worse. It is better off handled as a DAX measure or be pushed to the source.
  - chaitanya1
    Helper I
    1 year ago
    bhanu_gautam can you help me with that DAX i am not getting cumulative view through DAX.

Forum Discussion

Custom Column for Cummulative TR in power query

7 Replies

Related content

Cummulative YTD Totals By Month Error

Custom Column

cummulative sum not working

Cummulative Sum Calculated Column

Custom Column