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. I was able to do it with calculated column but wanted to know if there is any way to create using custom column?

 

Query used for calculated column -> 

 

Weekday = SWITCH(

                 WEEKDAY(table[timestamp.[Date],2),           

                 1,"1.Monday",

                 2,"2.Tuesday",

                 3,"3.Wednesday",

                 4,"4.Thursday",

                 5,"5.Friday",

                 6,"6.Saturday",

                 7,"7.Sunday")

 

Thanks,

Indhu.

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.

8 Replies

    • 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

    • indhu's avatar
      indhu
      Helper III
      8 years ago

      Thanks Phil_Seamark

       

      Actually, I have a date column which i am transforming in the following way,

      1. Duplicate the actual date column

      2. calculate the day of the week by right click -> Transform -> day -> day of week

      Now I have a column with a day of the week in numbers and wanted to transform that into the name. One option will be to use replace values option. But I feel it is not an efficient way to do it.

      Following your previous post, 

       

      = Table.DuplicateColumn(#"Renamed Columns2", "timestamp", "timestamp - Copy")

      This will be the actual code and when using the format option with this,

       

      = Table.DuplicateColumn(#"Renamed Columns2", "timestamp", format("timestamp - Copy","DDDD")

       I know something is wrong but can't find out what it is can you please put me in the right direction?

       

      Thanks,

      • anithat's avatar
        anithat
        Resolver II
        8 years ago

        Hi Indhu

         

        Have you tried to select "Name of Day" in the Transform option.

         

        1. Duplicate the actual date column

        2. calculate the day of the week by right click -> Transform -> day -> day of week  Name of Day

         

        Is that what you are Expecting?