Forum Discussion
Replace particular dates given by StartOfWeek (respectively Month) if certain criteria is violated
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
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 FirstOfWeekOrFirstOfMonthCopy 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
JimmyHello
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
6 Replies
Jimmy801Community Champion
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 FirstOfWeekOrFirstOfMonthCopy 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
JimmyDear 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
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