Replace particular dates given by StartOfWeek (respectively Month) if certain criteria is violated

[Anonymous]

Hello guys!

Already tried some things but couldn't succeed with following problem.

Working in advanced editor (but feel free to suggest a solution in e.g., measures), and if I want the first day of the week we have this very straightforward command Date.StartOfWeek which works like a charm:

IsoWeekly = Table.AddColumn(LastColumn, "ISO_Weekly", each Date.StartOfWeek([Date]), Date.Type)

But sometimes the first of the week is not in the same month let alone same year. For example the start of the week corresponding to the days from first to 5th of January 2020 would be 12/30/2019. Sometimes that is a problem.

So what if I want exactly is what I got most of the time in IsoWeekly above except for those dates as just described, here i want those particualr values replaced by simply the first of month. So basically: give me all Mondays as start of week except for those mondays that date back to old month, here simply overwrite by first of the current month.

I think the solution of this problem can serve as a blueprint to similar problems, e.g., replacing values in StartOfMonth.

Best,

Isidor

[Jimmy801 · Accepted solution]

Hello @Anonymous 

here the custom function for this

//FirstOfWeekOrFirstOfMonth
(dateint as date) as date =>

let
    FirstOfWeek = Date.StartOfWeek(dateint),
    FirstOfMonth = Date.StartOfMonth(dateint),
    FirstOfWeekOrFirstOfMonth = if Date.Month(dateint)= Date.Month(FirstOfWeek) then FirstOfWeek else FirstOfMonth
in
    FirstOfWeekOrFirstOfMonth

Copy paste this code to the advanced editor in a new blank query and rename the query to your desired function name. Add a new column in your original query and use this custom function.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801 · Accepted solution]

Hello @Anonymous 

here the custom function for this

//FirstOfWeekOrFirstOfMonth
(dateint as date) as date =>

let
    FirstOfWeek = Date.StartOfWeek(dateint),
    FirstOfMonth = Date.StartOfMonth(dateint),
    FirstOfWeekOrFirstOfMonth = if Date.Month(dateint)= Date.Month(FirstOfWeek) then FirstOfWeek else FirstOfMonth
in
    FirstOfWeekOrFirstOfMonth

Copy paste this code to the advanced editor in a new blank query and rename the query to your desired function name. Add a new column in your original query and use this custom function.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Dear @Jimmy801 ,

Thank you for your quick reply!

When I insert the custom function in the other query as below I get the following error: We cannot convert a value of type Record to type Date.

I did it the following way:

PreviousColumn = .....

NewColumn = Table.AddColumn(PreviousColumn, "NewColumn", FirstOfWeekOrFirstOfMonth),

...

Thanks a lot!

Isidor

[Jimmy801 · Accepted solution]

Hello

your code has to look like this (I don't know your Datecolumn)

NewColumn = Table.AddColumn(PreviousColumn, "NewColumn", each FirstOfWeekOrFirstOfMonth(_[YOURDATECOLUMNCOMESHERE]),

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Dear @Jimmy801 ,

out of curiosity, why does 

FirstOfWeekOrFirstOfMonth(_[YOURDATECOLUMNCOMESHERE])

 only function with the _ in there? took it out and it's failing.

Thanks 

[Jimmy801]

Hello @Anonymous 

in my setting

NewColumn = Table.AddColumn(PreviousColumn, "NewColumn", each FirstOfWeekOrFirstOfMonth(_[YOURDATECOLUMNCOMESHERE]),

you can eliminate the "_" for sure as a column-indicator without a record is automatically assigned to the input-record of the function, as the function starts with the each-keyword

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Crazily fast and on point, super support Jimmy!