WEEKDAY column convert numbers to text as days of week

[joannerw]

Hi. I have inserted a column to identify the days of the week, but I'd like to show them as text, rather than the number of their position in the week. Is this possible.

[V-lianl-msft · Accepted solution]

Hi @joannerw ,

Sorry, it was my mistake just now. I didn't notice the DQ connection mode you used.

You can use the "SWITCH" function to convert numbers to text.

Please try to use DAX like this:

weekday = SWITCH(test0109[Column],1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday")

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-lianl-msft]

Hi @joannerw ,

Click new column and enter the DAX formula:

Weekday = FORMAT ( 'Test'[Date], "dddd" )

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joannerw]

@V-lianl-msft this is the result:

[V-lianl-msft · Accepted solution]

Hi @joannerw ,

Sorry, it was my mistake just now. I didn't notice the DQ connection mode you used.

You can use the "SWITCH" function to convert numbers to text.

Please try to use DAX like this:

weekday = SWITCH(test0109[Column],1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday")

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joannerw · Accepted solution]

@V-lianl-msftthat worked perfectly. Thank you!!

[rajulshah]

Hello @joannerw,

Please use the following DAX formula:

Weekdays = FORMAT(Date[Date],"dddd")

Hope this helps. 

[itsme]

If you have a Date column, you can do this create a new column:

WeekdayText = FORMAT ( 'Table'[Date], "dddd" )

This will create a column with Sunday, Monday, Tuesday, etc. Use "ddd" if you want Sun, Mon, Tue....

Then highlight the new column, click the Column Tools tab, click the Sort by Column button and select the week number column you previously created. This will sort the text values, ensuring they are in proper order when you add them to a visual.

If this solves your question, please mark it as the solution. Thanks!

[joannerw]

Thanks for your response. I'm new to Power BI and not sure where I enter this formula. Is it through the New Column icon on the Calculations tab or the query editor. I attempted both but it didn't work for me. can you please provide more detail on how to use the formula??

[amitchandak]

In the DATA View, where you can see data. In-home tab , you have an option for new Column. Use that to have this formula

[joannerw]

I don't have the Data view available, only Report or Model. I'm using the desktop version and am running the September 2019 version and also using a direct query. Maybe I don't have access to the Data view.

[rajulshah]

In Direct Query, you won't be able to see the data. Did you try as I suggested?

[joannerw]

Again, i'm not sure where to use this formula. I'm unable to create a new table or see the data view....

[joannerw]

@rajulshah  this is how my Power BI Desktop appears:

[rajulshah]

Hello @joannerw,

Suppose your date table is named as 'Calendar', then you created a new calculated column in that table and use the formula as 

Weekdays = FORMAT(Calendar[Date],"dddd")

Please provide what error/result are you getting using this query.

PS:Calendar table can be created using CALENDAR DAX function.