cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
indhu Member
Member

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.

1 ACCEPTED SOLUTION

Accepted Solutions
anithat Regular Visitor
Regular Visitor

Re: custom column with switch statement

@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.

View solution in original post

8 REPLIES 8
Phil_Seamark Super Contributor
Super Contributor

Re: custom column with switch statement

Hi @indhu

 

You could try this syntax

 

Weekday = FORMAT('Table'[Date],"DDDD")

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

indhu Member
Member

Re: custom column with switch statement

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 Member
Member

Re: custom column with switch statement

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 Regular Visitor
Regular Visitor

Re: custom column with switch statement

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?

indhu Member
Member

Re: custom column with switch statement

@anithat thanks for your reply. What you mentioned will fetch day name in text. This is what i expect but this makes sorting difficult. 

 

The above result will only allow us to sort by alphabet which makes visuals weird. I wanted the visual to be ordered beginning from Monday. For this purpose, I used switch statement by manually using numbers at the beginning like "1.Monday". 

 

I did this in a calculated column. Now trying to figure out in a custom column

 

Thanks,

anithat Regular Visitor
Regular Visitor

Re: custom column with switch statement

@indhu

 

 create two columns

 

Day of Week which gives numeric value

Name of Day which gives Name

Apply & close

 

Go to  'Data' or 'Report' tab (on left side )

Go to 'Modelling' tab  (on Top)

Choose the column "Name of Day"

Click on 'Sort by Column'

From the dropdown list choose "Day of Week" and that will sort the "Name of Day"  by "Day of Week"

 

 

indhu Member
Member

Re: custom column with switch statement

@anithat thanks for your suggestion. The database is very huge and the refresh time now takes about 20-30 mins. I reckon adding one more column will add up the load. 

 

Is there any way I can use the switch statement in power query? 

 

I will test the refresh time for both as well. 

anithat Regular Visitor
Regular Visitor

Re: custom column with switch statement

@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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 849 guests
Please welcome our newest community members: