Forum Discussion

ankitkalsara's avatar
ankitkalsara
Icon for Helper I rankHelper I
4 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 = Quar...
  • AlexisOlson's avatar
    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] / 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"