Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Thanks!
Solved! Go to Solution.
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
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.
Hello @affan ,
I have the same problem but just with Quarterly data, with dates and values columns only. I tried to adjust the code above but it ends with error everytime.
My columns are: "Date" with Quarterly data formated like this: 01-Oct-18, 01-Jan-19 etc.
and "Core YoY Inflation" with Decimal Number format. I need to repeat the given month 3 times. not sum but average method. Also would be useful to know in which place of code do we change transformation method, sum or avg.
Can you give me a hint where should I change your code? and what should I change in case I have annual data instead of quarterly?
Best regards
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"
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.
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.
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
Works perfectly, thank you!!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |