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.
I have data with contract values that need to be spread over the length of a contract in PowerQuery. I found a solution online that used {[Start Date]..[End Date]} - but that's giving me every single day, whereas I need it on a monthly basis. I tried using Date.Month([Start Date]) but then it didn't count the time over different years.
For example,
Start Date | End Date | Value | Number of Months |
01/12/2020 | 03/12/2020 | 100 | 4 |
I would like to become this so I can spread the 100 over the 4 months to be 25 each.
Start Date | End Date | Value | Number of Months | Custom Date |
01/12/2020 | 03/12/2020 | 100 | 4 | 01/12/2020 |
01/12/2020 | 03/12/2020 | 100 | 4 | 01/01/2021 |
01/12/2020 | 03/12/2020 | 100 | 4 | 01/02/2021 |
01/12/2020 | 03/12/2020 | 100 | 4 | 01/03/2021 |
Solved! Go to Solution.
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzTSNzIwMlDSUQKyDIxBHEMQxwAkZKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t, #"Number of Months" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}, {"Number of Months", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(List.Dates([Start Date],Number.From([End Date]-[Start Date])+1,#duration(1,0,0,0)), each Date.Day(_) =1)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
#"Changed Type1"
Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.
Contact me privately for assistance with any large-scale BI needs, tutoring, etc.
Bless you
@Anonymous
It does start on the first month, as long as the start date is the first day of the month. If you want to add the first month even if the start date is not the first day of the month, just use Date.StartOfMonth([Start Date]) in the List.Dates
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzTSNzIwMlDSUQKyDIxBHEMQxwAkZKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t, #"Number of Months" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}, {"Number of Months", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(List.Dates(Date.StartOfMonth([Start Date]),Number.From([End Date]-[Start Date])+1,#duration(1,0,0,0)), each Date.Day(_) =1)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
#"Changed Type1"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
It does start on the first month, as long as the start date is the first day of the month. If you want to add the first month even if the start date is not the first day of the month, just use Date.StartOfMonth([Start Date]) in the List.Dates
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzTSNzIwMlDSUQKyDIxBHEMQxwAkZKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t, #"Number of Months" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}, {"Number of Months", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(List.Dates(Date.StartOfMonth([Start Date]),Number.From([End Date]-[Start Date])+1,#duration(1,0,0,0)), each Date.Day(_) =1)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
#"Changed Type1"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzTSNzIwMlDSUQKyDIxBHEMQxwAkZKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t, #"Number of Months" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}, {"Number of Months", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(List.Dates([Start Date],Number.From([End Date]-[Start Date])+1,#duration(1,0,0,0)), each Date.Day(_) =1)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
#"Changed Type1"
Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.
Contact me privately for assistance with any large-scale BI needs, tutoring, etc.
Bless you
@AlB oh wait sorry my only question is, it starts from the second month, how do I get it to start from the original month i.e 01/12/2020 - 01/03/2021 (starts December 2020 and ends in March 2021)?
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 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |