I used the following to add a Cohort Period column:
=Table.AddColumn (
#"Changed Type1" , "Cohort Subscription" , each Value.Divide ( ( [Subscription End Date] )-[Subscription Start Date] , 30 ) )
This results in values of 0, 1, 2, 3, 4, etc. How can I make 0=1, 1=2, etc? The user gets confused when period 0 is the first period so I a trying to start with the #1 as my first Period.
Thank you for any assistance with this.
HI @ScottTPA,
After the closing parenthesis of Value.Divide add + 1
=Table.AddColumn (
#"Changed Type1" , "Cohort Subscription" , each Value.Divide ( ( [Subscription End Date] )-[Subscription Start Date] , 30 ) +1 )I tried the following and I get ‘ERROR’ in each cell in the column,
#“Added Custom” = Table.AddColumn(#“Changed Type1”, “Cohort Subscription”, each Value.Divide(([Subscription End Date])-[Subscription Start Date],30)+1),
You shouldn’t need the comma at the very end, unless you have additional M code below it. Post the full M code so we can see what is throwing the error if removing the comma doesn’t work.
Thanks
Jarrett
let
Source = Excel.Workbook(File.Contents(“C:\Users\data.xlsx”), null, true),
#“Recurring Subscriptions_Sheet” = Source{[Item=“Recurring Subscriptions”,Kind=“Sheet”]}[Data],
#“Promoted Headers” = Table.PromoteHeaders(#“Recurring Subscriptions_Sheet”, [PromoteAllScalars=true]),
#“Changed Type” = Table.TransformColumnTypes(#“Promoted Headers”,{{“Subscription ID”, type text}, {“Customer ID”, type text}, {“Order ID”, type text}, {“Product ID”, type text}, {“Product Name”, type text}, {“Subscription Status”, type text}, {“Initial Price”, Currency.Type}, {“Recurring Price”, Currency.Type}, {“Payment Source”, type text}, {“Attached Card”, Int64.Type}, {“Subscription Created At”, type date}, {“Subscription Start Date”, type date}, {“Subscription End Date”, type any}, {“Number Of Charges”, Int64.Type}, {“Next Rebilling Date”, type date}, {“Total Failed Charges”, Int64.Type}, {“Frequency”, type text}, {“Customer First Name”, type text}, {“Customer Last Name”, type text}, {“Customer Email”, type text}, {“Customer Phone Number”, type any}, {“Billing Address Line 1”, type text}, {“Billing Address Line 2”, type any}, {“Billing Address City”, type text}, {“Billing Address State”, type text}, {“Billing Address Zip Code”, type any}, {“Billing Address Country”, type text}, {“Shipping Address Line 1”, type any}, {“Shipping Address Line 2”, type any}, {“Shipping Address City”, type text}, {“Shipping Address State”, type text}, {“Shipping Address Zip Code”, type any}, {“Shipping Address Country”, type text}, {“Affiliate ID”, Int64.Type}, {“Affiliate First Name”, type text}, {“Affiliate Last Name”, type text}, {“Affiliate Email”, type text}}),
#“Removed Columns” = Table.RemoveColumns(#“Changed Type”,{“Attached Card”}),
#“Replaced Value” = Table.ReplaceValue(#“Removed Columns”,“N/A”,#date(2030, 12, 31),Replacer.ReplaceValue,{“Subscription End Date”}),
#“Removed Columns1” = Table.RemoveColumns(#“Replaced Value”,{“Number Of Charges”, “Total Failed Charges”, “Customer Email”, “Customer Phone Number”, “Billing Address Line 1”, “Billing Address Line 2”, “Billing Address City”, “Billing Address Country”, “Shipping Address Line 1”, “Shipping Address Line 2”, “Shipping Address City”, “Shipping Address Country”, “Affiliate Email”, “Affiliate First Name”, “Affiliate Last Name”}),
#“Changed Type1” = Table.TransformColumnTypes(#“Removed Columns1”,{{“Subscription End Date”, type date}}),
#“Added Custom” = Table.AddColumn(#“Changed Type1”, “Cohort Subscription”, each Value.Divide(([Subscription End Date])-[Subscription Start Date],30)),
#“Changed Type4” = Table.TransformColumnTypes(#“Added Custom”,{{“Cohort Subscription”, Int64.Type}}),
#“Renamed Columns” = Table.RenameColumns(#“Changed Type4”,{{“Cohort Subscription”, “Cohort Subscription Periods”}}),
#“Removed Columns2” = Table.RemoveColumns(#“Renamed Columns”,{“Customer First Name”, “Customer Last Name”})
in
#“Removed Columns2”
Can you give this a go?
let
Source = Excel.Workbook(File.Contents("C:\Users\data.xlsx"), null, true),
#"Recurring Subscriptions_Sheet" = Source{[Item="Recurring Subscriptions",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Recurring Subscriptions_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Subscription ID", type text}, {"Customer ID", type text}, {"Order ID", type text}, {"Product ID", type text}, {"Product Name", type text}, {"Subscription Status", type text}, {"Initial Price", Currency.Type}, {"Recurring Price", Currency.Type}, {"Payment Source", type text}, {"Attached Card", Int64.Type}, {"Subscription Created At", type date}, {"Subscription Start Date", type date}, {"Subscription End Date", type any}, {"Number Of Charges", Int64.Type}, {"Next Rebilling Date", type date}, {"Total Failed Charges", Int64.Type}, {"Frequency", type text}, {"Customer First Name", type text}, {"Customer Last Name", type text}, {"Customer Email", type text}, {"Customer Phone Number", type any}, {"Billing Address Line 1", type text}, {"Billing Address Line 2", type any}, {"Billing Address City", type text}, {"Billing Address State", type text}, {"Billing Address Zip Code", type any}, {"Billing Address Country", type text}, {"Shipping Address Line 1", type any}, {"Shipping Address Line 2", type any}, {"Shipping Address City", type text}, {"Shipping Address State", type text}, {"Shipping Address Zip Code", type any}, {"Shipping Address Country", type text}, {"Affiliate ID", Int64.Type}, {"Affiliate First Name", type text}, {"Affiliate Last Name", type text}, {"Affiliate Email", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attached Card"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","N/A",#date(2030, 12, 31),Replacer.ReplaceValue,{"Subscription End Date"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"Number Of Charges", "Total Failed Charges", "Customer Email", "Customer Phone Number", "Billing Address Line 1", "Billing Address Line 2", "Billing Address City", "Billing Address Country", "Shipping Address Line 1", "Shipping Address Line 2", "Shipping Address City", "Shipping Address Country", "Affiliate Email", "Affiliate First Name", "Affiliate Last Name"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Subscription End Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Cohort Subscription", each Number.From(Value.Divide(([Subscription End Date])-[Subscription Start Date],30))+1),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom",{{"Cohort Subscription", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type4",{{"Cohort Subscription", "Cohort Subscription Periods"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Customer First Name", "Customer Last Name"})
in
#"Removed Columns2"@Melissa , this works! Thank you for all of your help. 
Sorry it took awhile to reply. I took a break from PowerBI for the weekend.