cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JMelo Frequent Visitor
Frequent Visitor

Power Query: Count Months within Date Interval, by Year

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

4 REPLIES 4
Community Support Team
Community Support Team

Re: Power Query: Count Months within Date Interval, by Year

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JMelo Frequent Visitor
Frequent Visitor

Re: Power Query: Count Months within Date Interval, by Year

The desired solution would to add one column for each year within the date interval.

 

Power Query Table as is in the Database:

 

Contract NameStart DateEnd Date
Test 12304/01/201612/01/2018

 

Power Query Table after data modeling:

 

Contract NameStart DateEnd DateMonths in 2016Months in 2017Months in 2018
Test 12304/01/201612/01/201891212
JMelo Frequent Visitor
Frequent Visitor

Re: Power Query: Count Months within Date Interval, by Year

Any help please?

Highlighted
sanimesa Member
Member

Re: 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"