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 team,
I want to split my Quota data from Quarterly into monthly data by adding 2 new columns.
Attaching here pbix file. Any help will be much appreciated.
I checked the similar post: Solved: Add new rows to transform quarterly and yearly dat... - Microsoft Power BI Community but it seems it is working only for 1 year data.
Solved! Go to Solution.
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"
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"
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 |
---|---|
100 | |
99 | |
76 | |
66 | |
59 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |