topic Re: Conditional column between 2 dates in Power Query

Conditional column between 2 dates

Anonymous — Thu, 30 Jul 2020 08:31:20 GMT

Hello. I am trying to calculate a conditional column which returns a name if a date is between 2 dates.

My data looks like this in one column:

Date
2020-05-27
2020-05-28
2020-05-29
2020-05-30
2020-05-31
2020-06-01
2020-06-02
2020-06-03
2020-06-04
2020-06-05
2020-06-06
2020-06-07
2020-06-08
2020-06-09
2020-06-10

I would like to be able to add a name 'week X' for dates between a certain period. Like this:

Date	Week
2020-05-27	Week 1
2020-05-28	Week 1
2020-05-29	Week 1
2020-05-30	Week 1
2020-05-31	Week 1
2020-06-01	Week 1
2020-06-02	Week 1
2020-06-03	Week 2
2020-06-04	Week 2
2020-06-05	Week 2
2020-06-06	Week 2
2020-06-07	Week 2
2020-06-08	Week 2
2020-06-09	Week 2
2020-06-10	Week 3

Thanks

Re: Conditional column between 2 dates

Anonymous — Thu, 30 Jul 2020 08:41:09 GMT

Add conditional Column,

= Table.AddColumn(#"Changed Type1", "Custom", each if [Date] >= #date(2020, 6, 10) then "Week 3" else if [Date] >= #date(2020, 6, 3) then "Week 2" else if [Date] >= #date(2020, 5, 27) then "Week 1" else null)

Re: Conditional column between 2 dates

Anonymous — Thu, 30 Jul 2020 09:09:57 GMT

Actually, this will work nicer. Just go to add custom column then copy in this.

Date.WeekOfYear(Date.AddWeeks([Date],-21),Day.Wednesday)

You can also download the test PBIX doc I created to see how I've achieved it.

https://1drv.ms/u/s!AnIEh6WhI4JogrAPf3w8FrS2QvihAQ?e=GmWNsC

s, Re: Conditional column between 2 dates

Anonymous — Thu, 30 Jul 2020 11:26:40 GMT

Hi Karlos. This has kind of worked, but for any date before the 28th May (when the programme started) its showing incorrect weeks. Is there a way so it will show null for anything before the start date?

Thanks

Re: s, Re: Conditional column between 2 dates

Anonymous — Thu, 30 Jul 2020 11:45:16 GMT

@Anonymous here you go. I've also updated the one drive file.

= Table.AddColumn(#"Changed Type1", "Custom.2", each if [Date] > #date(2020,5,27) then Date.WeekOfYear(Date.AddWeeks([Date],-21),Day.Wednesday) else null)

if [Date] > #date(2020,5,27) then Date.WeekOfYear(Date.AddWeeks([Date],-21),Day.Wednesday) else null

If this solution works for you, please mark it as accepted and hit that thumbs up.

Re: s, Re: Conditional column between 2 dates

Anonymous — Thu, 30 Jul 2020 11:55:02 GMT

Worked perfectly. Thank you.