Add new rows from Quarterly Data to Monthly Data

[ankitkalsara]

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 = Quarterly Quota Amount / 3.

Attaching here pbix file. Any help will be much appreciated. 

I checked the similar post: Solved: Add new rows to transform quarterly and yearly dat... - Microsoft Power BI Community but it seems it is working only for 1 year data.

[AlexisOlson · Accepted solution]

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] / 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"

[ankitkalsara]

Thank you so much @AlexisOlson for the solution 👍