number of weeks in a month|Power Query|Excel Forum|My Online Training Hub

Hi,

I have a problem with power query that is driving me crazy. What I’m after is automatically calculating the number of weeks included in a month. Hold on, it sounds easy but it is not.

I have a column with "plans_week1", "plans_week2" and each refers to a date that is the end of the working week (in consecutive order) - for a total of 1 year. So first, I need to calculate the date that is the end of the relevant working week, then calculate the month that the "week-end" date falls into, and finally, calculate the number of weeks in that corresponding month (which is then used for other calculations). I can't just hard-code the number of weeks for each month because this is a real-time model and needs to be based on how many weeks are left in real time in the current month (and future months).

With the help on a user in a forum we have managed to reach the result in the attached spreadsheet. The data model works well and it calculates the correct number of weeks. It does so even with plans_week* duplications but everything goes pear shaped UNTIL… additional columns are added in the source table. Obviously this is happening because the CountIF formula built in power query is not considering only the “weeks” column but the whole table. I really can’t figure out a way of fixing this…. Is there anyone here able to help me?