Power Query: Count Months within Date Interval, by Year

[JMelo]

Good evening eveyone!

I have a challenge where i need to count the amount of months each row

For each row i have a start date and end date.

Start Date: 04/01/2016
End Date: 12/01/2018

Answer:

Add a Column "Year 2016" with 9 months
Add a Column "Year 2017" with 12 months

Add a Column "Year 2018" with 12 months

Please note Start Date and End Date may vary to any year (before 2016 or after 2018).

Could you please help me?

Thank you!

JMelo

[v-danhe-msft]

Hi @JMelo ,

What do you mean about below information?

Add a Column "Year 2016" with 9 months
Add a Column "Year 2017" with 12 months

Add a Column "Year 2018" with 12 months

Could you please post your desired result if possible?

Regards,

Daniel He

[JMelo]

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

[JMelo]

Any help please?

[sanimesa · Accepted solution]

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"