Need M code for calculating next week Monday date in Custom Column

[Kelly A]

If the current date is 5/12/2023 I would want the custom column to display 5/15/2023 (this data will be dynamic each week).

 

[p45cal]

= Table.AddColumn(#"Changed Type", "Next Monday", each Date.AddDays(Date.EndOfWeek([Current Date]),2), type date)

 

[Kelly A]

thank you! How can the above M code be updated so that it doesn't figure the custom column on a date column, but uses the current date instead? I tried removing column [Current Date] and replaced it with FixedLocalNow() but not working

 

[p45cal]

= Table.AddColumn(#"Changed Type", "Next Monday", each Date.AddDays(Date.EndOfWeek(DateTime.Date(DateTime.FixedLocalNow())),1),type date)

 

[Kelly A]

Thank you so much!!!

 

[Kelly A]

So I ran into a problem... the above formula does give me what I need to see BUT there is a row count issue. After I did a merge queries my row count was correct at 517 (in real world) but after I expand that custom column to show only the Next Monday custom column, I am getting in excess of 267,000 rows. I attached a couple of screenshots.

 

[GraH - Guido]

So I ran into a problem... the above formula does give me what I need to see BUT there is a row count issue. After I did a merge queries my row count was correct at 517 (in real world) but after I expand that custom column to show only the Next Monday custom column, I am getting in excess of 267,000 rows. I attached a couple of screenshots.

Means you have a one to many result with the merge. If you'd click in the white zone next to a table you can see the preview below. That Will give you a hint. I can't access your file on my phone, so cannot elaborate more for now.

 

[Kelly A]

My row count was correct at 517 after the merge. Only after I applied the custom column with the formula, then 267,000+ rows was displayed.

 

[p45cal]

Can't advise with only pictures.

 

[Kelly A]

I am attaching the actual file, please delete it when we are done. There are 2 queries in the workbook. When refreshing the Temp query, the row count increases dramatically at the 'Expanded Custom' Applied Step. Thank you in advance for all of your support!

I got an error message upon upload, "The uploaded file is too large for the server to process." I will try and give you a better indication with new screenshots. Please note after merging queries, I still had a row count of 517.

Please let me know if you need more information or screenshots of something else. I apologize but this is the best I can do.

 

[p45cal]

Could you link to a workbook on a file sharing site? There are loads of them (what's more, you'll be in control of their availability).

 

[Debaser]

Why are you referencing the Changed Type step in the added column formula rather than the previous step?

 

[Kelly A]

ok guys, I used the code on my sample file, the data originally has 11 rows, after applying the code and expanding the column, I have 121 rows. I have attached the sample file with the code and query.

 

[Kelly A]

Why are you referencing the Changed Type step in the added column formula rather than the previous step?

That is a formula I got in this chat to use for... "How can the above M code be updated so that it doesn't figure the custom column on a date column, but uses the current date instead? I tried removing column [Current Date] and replaced it with FixedLocalNow() but not working."

Another member provided the formula as I am a new pq user. If you have the correct formula for this, please advise me.

 

[p45cal]

Is the attached what you want?

 

[Kelly A]

That is perfect now, thank you so much for your assistance! Have a blessed day!