Dynamically removing Weekcommencing date

Power Query
1

Hi Everyone,

I have a scenario to do with calendar week commencing dates.

Basically I am currently using the ultimate date calendar to filter a fact table with WC dates but I have an issue were a company in another country is creating a new week
that is out of sync with the whole company. Here is the example

image
image801×619 13.7 KB

For Feb as you can see the difference between the WC for each row is 7 apart from
22/02 and 21/02 were the difference is 1 and the 28/02 and 21/02 were
the difference is 6. The 22/02 is a new week that was created by this company which is affecting the structure of the WC for all countries. I want a way to dynamically ignore or remove this WC i.e 22/02 were the difference between the WC is not 7.

Please note I have tried creating an index column and then looking up the previous row
so I could subtract to get the number of days but this is extremely slow as we are dealing with millions of rows and multiple employees. The sample file I have given is just for one employee.

I have highlighted in red and yellow the rows I want to remove. I want to keep the rows for which the WC dates diff to the previous row is 7 days.

I hope this is clear and appreciate your help in advance.

Here is the sample file attach

Remove WC dates.xlsx (10.7 KB)

Thanks

Patrick

2

Hi @ambepat,

Give this a go, just copy the full script into a new blank query.
Instead of comparing each row to the previous value, I think testing wether the date is a monday should do the trick…

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJbCsNACIXhveS5wqhzcy2h+99GTWnroZy8DT8fGRNznseQJdZMj8ehCmebEeN4Ps48Qt6cRGUblNiELJuJqpuCgDqo0FnZjN/iQIISb5WHBBETKxXqkIMSa5VtcrIqu2gjpGNu3ORioa8btKFb5yiXW9315kmSF37ygjOQjblzkzuubspRbhnQvkEBPZ9qdKWYucmlQg+Ocq3Vbd6ghUjew9nfX1g1KLi2/cuTk1XZnJJr09/sjU+ilacYEQuqU5FL/uX8QozkiossTgzI5gTyFKfDOgzLRA7rMCwjOazDsJSMyq6cbCDSiehQGxX5Z1denOzK1inB7PyifJ/KMv7F8wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WeekCommencing = _t, EndDate = _t, Employee = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Employee", Int64.Type}, {"WeekCommencing", type date}, {"EndDate", type date}}),
    KeepMondays = Table.SelectRows(ChType, each ( Date.DayOfWeek( [WeekCommencing], Day.Monday )=0 ))
in
    KeepMondays

I hope this is helpful

1 Like
3

@Melissa thanks for this. It works and I can see it is more elegant than the group rows solution condition you had earlier. I have some other thoughts about this in terms of what if I want to keep the weeks that were actually created i.e the ones that are not mondays but only keep those weeks for which there isn’t an overlap with the normal weekcommencing on Mons i.e if we look at Feb the new week that was created is WC 22/02 and it overlaps with the previous WC 21/02 and the next WC 28/02. What if I want to keep that WC 22/02 and remove WC 21/02 and WC 28/02. So I want to keep the new WC 22/02 that is not a Mon and keep the other WC that is a Mon as long as it does not overlap with the new week that was created. If this is too much then don’t bother not really required but I just thought it is an interesting problem to solve. Thanks again for your elegant power query skills/prowess. Never ceases to amaze!!!

4

Hi @ambepat,

Only you can judge what makes sense for your business but :thinking: in terms of data consistancy and modelling that would really confuse me… so I would enforce the start of week day as week commencing date.
Just my 2 cents.