Reply
Frequent Visitor
Posts: 7
Registered: ‎12-31-2018
Accepted Solution

Add new rows to transform quarterly and yearly data to monthly

Hi. I'm new to PowerBI and can't find the way do do this.

 

I have data with a mix of monthly, quarterly and yearly data, and I need to transform everything into monthy data, by repeating the Quarterly data 3 times (dividing the ammounts by 3) and the yearly data 12 times (dividing ammounts by 12). The upper example table should be transformed in the lower table. How could I make this happen in the PowerBI editor?

 

Captura.PNG 

Thanks!


Accepted Solutions
Highlighted
Established Member
Posts: 203
Registered: ‎07-12-2017

Re: Add new rows to transform quarterly and yearly data to monthly

[ Edited ]

Hi @eyesem,

 

You can add a function to get the start and end of the period as below. Then add the required table as reference. See the details below and use the code shared below.

 

 

                

 

 

 

 

 

Create a new function as belwo and rename it to fxMonthsCount

(start as date, end as date) as table =>
let
 
 Source = {Number.From(start)..Number.From(end)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "StartDate"}}),

    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "StartDate", "EndDate"),

    #"Calculated End of Month" = Table.TransformColumns(#"Duplicated Column",{{"StartDate", Date.EndOfMonth, type date}}),
  #"Calculated End of Month1" = Table.TransformColumns(#"Calculated End of Month",{{"EndDate", Date.StartOfMonth, type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Calculated End of Month1"),
    #"Inserted End of Year" = Table.AddColumn(#"Removed Duplicates", "EndOfYear", each Date.EndOfYear([StartDate]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Year", {}, {{"Table", each _, type table}, {"Months_Total", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

 

Use the below Code as shown in the illustration above to split the rows.

 

 AddedCustTable = Table.AddColumn(Source, "Split", each fxMonthsCount([Date_ini],[Date_end])),
    #"Expanded Split" = Table.ExpandTableColumn(AddedCustTable, "Split", {"Table", "Months_Total"}, {"Table", "Months_Total"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Expanded Split", "Table", {"StartDate", "EndDate"}, {"StartDate", "EndDate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table", "QtyMonthly", each [Quantity]/[Months_Total], type number)
in
    #"Added Custom"

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

 

 

 

 

View solution in original post

Super User
Posts: 4,103
Registered: ‎01-14-2017

Re: Add new rows to transform quarterly and yearly data to monthly

Hi,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Type", type text}, {"Date_ini", type datetime}, {"Date_end", type datetime}, {"Quantity", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each if [Type]="M" then 1 else if [Type]="Q" then 3 else 12, type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each {Number.From(1)..[Duration]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Months to advance by"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Months to advance by", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom", each Date.AddDays(Date.AddMonths([Date_ini],[Months to advance by]),-1), type date),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.1", each [Quantity]/[Duration], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Date_end", "Quantity", "Duration", "Months to advance by"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date_end"}, {"Custom.1", "Quantity"}})
in
    #"Renamed Columns1"

Hope this helps.

 

Untitled.png

View solution in original post


All Replies
Highlighted
Established Member
Posts: 203
Registered: ‎07-12-2017

Re: Add new rows to transform quarterly and yearly data to monthly

[ Edited ]

Hi @eyesem,

 

You can add a function to get the start and end of the period as below. Then add the required table as reference. See the details below and use the code shared below.

 

 

                

 

 

 

 

 

Create a new function as belwo and rename it to fxMonthsCount

(start as date, end as date) as table =>
let
 
 Source = {Number.From(start)..Number.From(end)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "StartDate"}}),

    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "StartDate", "EndDate"),

    #"Calculated End of Month" = Table.TransformColumns(#"Duplicated Column",{{"StartDate", Date.EndOfMonth, type date}}),
  #"Calculated End of Month1" = Table.TransformColumns(#"Calculated End of Month",{{"EndDate", Date.StartOfMonth, type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Calculated End of Month1"),
    #"Inserted End of Year" = Table.AddColumn(#"Removed Duplicates", "EndOfYear", each Date.EndOfYear([StartDate]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Year", {}, {{"Table", each _, type table}, {"Months_Total", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

 

Use the below Code as shown in the illustration above to split the rows.

 

 AddedCustTable = Table.AddColumn(Source, "Split", each fxMonthsCount([Date_ini],[Date_end])),
    #"Expanded Split" = Table.ExpandTableColumn(AddedCustTable, "Split", {"Table", "Months_Total"}, {"Table", "Months_Total"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Expanded Split", "Table", {"StartDate", "EndDate"}, {"StartDate", "EndDate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table", "QtyMonthly", each [Quantity]/[Months_Total], type number)
in
    #"Added Custom"

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

 

 

 

 

Super User
Posts: 10,775
Registered: ‎07-11-2015

Re: Add new rows to transform quarterly and yearly data to monthly

OK I spent some on this and didn't get as far as I would have liked but I relay what I did get done. I foresee major issues with this approach however when you get more than one year or quarter in there. But here goes. My idea was to create 3 tables, one for each type and UNION them together. Not entirely successful.

 

Table4Months = FILTER(ALL('Table4'),[Type]="M")


Table4Quarters = 
VAR __table = FILTER(ADDCOLUMNS('Table4',"__Quarter",ROUNDUP(MONTH([Date_ini])/3,0)),[Type]="Q")
VAR __beginDate = MAXX(__table,[Date_ini])
VAR __endDate = MAXX(__table,[Date_end])
VAR __quarter = MAXX(__table,[__Quarter])
VAR __year = YEAR(__beginDate)
VAR __table1 = SELECTCOLUMNS(FILTER('Calendar',YEAR([Date])=__year && [Quarter]=__quarter && DAY([Date])=1),"Date_ini",[Date],"Date_end",[MonthEnd])
VAR __average = SUMX(__table,[Quantity])/3
VAR __id = MAXX(__table,[Id])
VAR __type = "Q"
RETURN
SELECTCOLUMNS(ADDCOLUMNS(__table1,"Quantity",__average,"Id",__id,"Type",__type),"Id",[Id],"Type",[Type],"Date_ini",[Date_ini],"Date_end",[Date_end],"Quantity",[Quantity])


Table4Years = 
VAR __table = FILTER('Table4',[Type]="Y")
VAR __year = YEAR(MAXX(__table,[Date_ini]))
VAR __table1 = FILTER('Calendar',YEAR([Date])=__year && DAY([Date])=1)
VAR __average = SUMX(__table,[Quantity])/12
VAR __id = MAXX(__table,[Id])
VAR __type = "Y"
RETURN
SELECTCOLUMNS(ADDCOLUMNS(__table1,"Quantity",__average,"Id",__id,"Type",__type),"Id",[Id],"Type",[Type],"Date_ini",[Date],"Date_end",[MonthEnd],"Quantity",[Quantity])


Table4Final = UNION('Table4Months','Table4Quarters','Table4Years')

Uploading PBIX for reference. Nasty problem.

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Attachment
Super User
Posts: 4,103
Registered: ‎01-14-2017

Re: Add new rows to transform quarterly and yearly data to monthly

Hi,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Type", type text}, {"Date_ini", type datetime}, {"Date_end", type datetime}, {"Quantity", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each if [Type]="M" then 1 else if [Type]="Q" then 3 else 12, type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each {Number.From(1)..[Duration]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Months to advance by"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Months to advance by", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom", each Date.AddDays(Date.AddMonths([Date_ini],[Months to advance by]),-1), type date),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.1", each [Quantity]/[Duration], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Date_end", "Quantity", "Duration", "Months to advance by"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date_end"}, {"Custom.1", "Quantity"}})
in
    #"Renamed Columns1"

Hope this helps.

 

Untitled.png

Frequent Visitor
Posts: 7
Registered: ‎12-31-2018

Re: Add new rows to transform quarterly and yearly data to monthly

Works perfectly, thank you!!

Frequent Visitor
Posts: 4
Registered: ‎01-04-2019

Re: Add new rows to transform quarterly and yearly data to monthly

Solution above. Here adjusted for educationl purpose

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc1BCsAgDATAv+QsxGyito/ooUcR//+NBttqe1jYhSFpjYQCHR6wMKLso2J7e7JSqIdG8HF6ZDllnV0UdkP1Vf9Q8JEpm2JIe17nJTNrnOcBd/0C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Type = _t, Date_ini = _t, Date_end = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Type", type text}, {"Date_ini", type date}, {"Date_end", type date}, {"Quantity", Int64.Type}}),
#"Added Duration" = Table.AddColumn(#"Changed Type", "Duration", each if [Type]="M" then 1 else if [Type]="Q" then 3 else 12, type number),
#"Added Months to Advance by" = Table.AddColumn(#"Added Duration", "Months to advance by", each {Number.From(1)..[Duration]}),
#"Expanded list" = Table.ExpandListColumn(#"Added Months to Advance by", "Months to advance by"),
#"Changed list to Number" = Table.TransformColumnTypes(#"Expanded list",{{"Months to advance by", Int64.Type}}),
#"Added New Date ini" = Table.AddColumn(#"Changed list to Number", "Date ini", each Date.AddDays(Date.AddMonths([Date_ini],[Months to advance by]-1),0), type date),
#"Added New Date end" = Table.AddColumn(#"Added New Date ini", "Date end", each Date.AddDays(Date.AddMonths([Date_ini],[Months to advance by]),-1), type date),
#"Added New Quantity" = Table.AddColumn(#"Added New Date end", "Quantity ", each [Quantity]/[Duration], type number),
#"Removed Old Columns" = Table.RemoveColumns(#"Added New Quantity",{"Date_end", "Quantity", "Duration", "Months to advance by", "Date_ini"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Old Columns",{{"Date ini", "Date_ini"}, {"Date end", "Date_end"}, {"Quantity ", "Quantity"}})
in
#"Renamed Columns"