Forum Discussion
Power Query: Count Months within Date Interval, by Year
Not a complete solution!
I am assuming you want a generic way to add columns given any two dates. You can use List.Generate to run a loop for all the years between start and end dates and then within the loop add the column. A reference for the concept is below:
https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/
As for syntax of adding the column you require, you can play with the Add Column from the menu options. Here is an example (after manually entering data):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwNDJW0lEyMNE3MNQ3MjA0A3IMjaAcC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Contract Name" = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract Name", type text}, {"Start Date", type date}, {"End Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Months in 2016", each 12 - Date.Month([Start Date]))
in
#"Added Custom"
The desired solution would to add one column for each year within the date interval.
Power Query Table as is in the Database:
| Contract Name | Start Date | End Date |
| Test 123 | 04/01/2016 | 12/01/2018 |
Power Query Table after data modeling:
| Contract Name | Start Date | End Date | Months in 2016 | Months in 2017 | Months in 2018 |
| Test 123 | 04/01/2016 | 12/01/2018 | 9 | 12 | 12 |
Any help please?
Not a complete solution!
I am assuming you want a generic way to add columns given any two dates. You can use List.Generate to run a loop for all the years between start and end dates and then within the loop add the column. A reference for the concept is below:
https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/
As for syntax of adding the column you require, you can play with the Add Column from the menu options. Here is an example (after manually entering data):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwNDJW0lEyMNE3MNQ3MjA0A3IMjaAcC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Contract Name" = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract Name", type text}, {"Start Date", type date}, {"End Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Months in 2016", each 12 - Date.Month([Start Date]))
in
#"Added Custom"