Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |