Forum Discussion

indhu's avatar
indhu
Helper III
8 years ago
Solved

custom column with switch statement

Hi,    I would like to know if there is any way to create a custom column with a switch statement.   Requirement -> I have a date column and wanted to calculate the day of week column with a name...
Need Help
  • anithat's avatar
    anithat
    8 years ago

    indhu

     

    Then the Switch statement that you mentioned is working perfect right?

     

    In this case as well, create two columns 

     

    Name of Day

     = SWITCH(
                     WEEKDAY(table[Date],2),         
                     1,"Monday",
                     2,"Tuesday",
                     3,"Wednesday",
                     4,"Thursday",
                     5,"Friday",
                     6,"Saturday",
                     7,"Sunday")

    and 

    Day of Week = WEEKDAY(table[Date],2)

     

    Then use Sort by column option as suggested before.

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi indhu

 

You could try this syntax

 

Weekday = FORMAT('Table'[Date],"DDDD")
indhu's avatar
indhu
Helper III
8 years ago

thanks Phil_Seamark. 

 

Actually, I have a date column and transformed that into a calculated day of the week by following,

1. Duplicated the column

2. right click -> transform -> Day -> day of week

 

after the above step i have this formula in the bar,

 

= Table.TransformColumns(#"Duplicated Column",{{"timestamp - Copy", Date.DayOfWeek, Int64.Type}}).

 

If I follow as you suggested, 

 

= Table.TransformColumns(#"Duplicated Column",format{{"timestamp - Copy", Date.DayOfWeek, Int64.Type,"DDDD"}}).

 

This doesn't work. I know I am missing something stupid. Can you please help?

 

I read in a post that custom column is more efficient than a calculated column. So thought I can try that

 

Thanks again,

Indhu