Forum Discussion
ankitkalsara
Helper I
4 years agoAdd new rows from Quarterly Data to Monthly Data
Hi team, I want to split my Quota data from Quarterly into monthly data by adding 2 new columns. Monthly Start Date = 1st date of each month (3 rows per quarter) Monthly Quota Amount = Quar...
- 4 years ago
Add two new columns with these formulas:
Monthly Start Date = {[Start Date], Date.AddMonths([Start Date], 1), Date.AddMonths([Start Date], 2)} Monthly Quota Amount = [Quota Amount] / 3The first one returns a list which you can then expand into multiple rows.
Full query M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9Q30jAyMDENPUAAiUYnWilYygsiZYZY2hsuZYZU1gJhtglTZFtRjkDiMTuKwZqsVosuaoFqPJWqBZjCwdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record Id" = _t, Owner = _t, #"Start Date" = _t, #"Quota Amount" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record Id", Int64.Type}, {"Owner", type text}, {"Start Date", type date}, {"Quota Amount", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthlyStartDate", each {[Start Date], Date.AddMonths([Start Date], 1), Date.AddMonths([Start Date], 2)}, type list), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Monthly Quota Amount", each [Quota Amount] / 3, Int64.Type), #"Expanded MonthlyStartDate" = Table.ExpandListColumn(#"Added Custom1", "MonthlyStartDate"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded MonthlyStartDate",{{"MonthlyStartDate", type date}}) in #"Changed Type1"
AlexisOlson
Super User
4 years agoAdd two new columns with these formulas:
Monthly Start Date =
{[Start Date], Date.AddMonths([Start Date], 1), Date.AddMonths([Start Date], 2)}
Monthly Quota Amount =
[Quota Amount] / 3
The first one returns a list which you can then expand into multiple rows.
Full query M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9Q30jAyMDENPUAAiUYnWilYygsiZYZY2hsuZYZU1gJhtglTZFtRjkDiMTuKwZqsVosuaoFqPJWqBZjCwdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record Id" = _t, Owner = _t, #"Start Date" = _t, #"Quota Amount" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Record Id", Int64.Type}, {"Owner", type text}, {"Start Date", type date}, {"Quota Amount", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "MonthlyStartDate", each {[Start Date], Date.AddMonths([Start Date], 1), Date.AddMonths([Start Date], 2)}, type list),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Monthly Quota Amount", each [Quota Amount] / 3, Int64.Type),
#"Expanded MonthlyStartDate" = Table.ExpandListColumn(#"Added Custom1", "MonthlyStartDate"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded MonthlyStartDate",{{"MonthlyStartDate", type date}})
in
#"Changed Type1"
- ankitkalsara4 years ago
Helper I
Thank you so much AlexisOlson for the solution 👍