Forum Discussion

ankitkalsara's avatar
ankitkalsara
Icon for Helper I rankHelper I
5 years ago
Solved

Add 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 = 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.

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 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] / 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"

     

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 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] / 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"