cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eyesem
Frequent Visitor

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
affan Solution Sage
Solution Sage

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

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 IV
Super User IV

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Highlighted
affan Solution Sage
Solution Sage

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

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 IV
Super User IV

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.

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

eyesem
Frequent Visitor

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

Works perfectly, thank you!!

Ola_S
Frequent Visitor

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"

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors