Assign a number to Day of Week using Case in Data Studio

Question

I'm trying to assign a number to a Day of Week field in Data Studio. So for Monday I want to return 1, Tuesday 2, Wednesday 3 and so on where Sunday will be 7.

I have written the below case statement which is accepted by Data Studio but when I add the field to a Table or Pivot Table all days return a null value.

This is the Case Statement

And this is what is returned when adding the Day Number field to a pivot table. The 3rd column in the Day Number field.

Any ideas how I can achieve this?

Thanks in advance.

Answer 1

EXTRACT(DAYOFWEEK FROM datetime_expression)