Convert Day Number to Day of Week

[MarkSL]

Hi,

I am new to Power BI and this is my first post, so apologies if I am asking something obvious!

I want to create a graph, showing a summary of bookings taken across a week, with the Day of the Week being on the X axis.  This will give the business an idea of patterns seen over a long period of time, eg Mondays are typically more busy that Fridays etc.

My imported dataset is already aggregated and contains a column 'BookingDayNo' which contains values 1 to 7.  I want to convert these values to Days of the Week, eg 1 = Sunday, 2 = Monday etc.  I want Power BI to recognise my data as Days Of the Week, so that when I graph it, the days are in the correct order along the X Axis and not alphabetical.   Eg Mon-Tue-Wed-Thu-Fri.

In Excel I would simply convert the numbers to Days using the special format code of 'DDD'.  Excel then recognises these as days and graphs them in the correct order.  

I have searched the forum and the nearest I can find is FORMAT(Dates[Date],"DDDD"), but my data is not at the transaction level and so I do not have a standard date to convert.  The originating dataset is over 100k rows, hence why it is already summarised in a stored procedure before being imported into Power BI.

Thoughts appreciated!

Also, hopefully a simple one, but when I am in the Power BI Desktop and I want to start something all over again, I can't seem to find a simple 'Close' option.  I can only see the option to Exit, which requires me to restart the software and sign back in again?

Thanks

Mark

[jthomson]

If it's mostly so that you can make nice looking graphs, why not throw together a very simple two column table with 1-7 in one column and Monday-Sunday in another, relate it to your data table and then use the column with the full names of the days in your axis? If for whatever reason this then sorts alphabetically, you can just sort by the number column

[MarkSL]

Hi, 

Thanks for your reply, I have tried this, however the order of the data has no affect on the order of the values on the X Axis, I still get Fri-Mon-Sat-Sun-Thu-Tue-Wed.  Additionally, I can't see a way to add Day Number as a hidden field on the X Axis and sort by it?

An example of the data is:

Site	Day Of Week	Bookings
A	1		21
A	2		3	
A	3		5	
A	4		17	
A	5		22	
B	1		3
B	2		24	
B	3		5	
B	4		44	
B	5		18	
etc

So I want my graph to look like - taken from Excel where it works 🙂

Hopefully someone has an idea! Really hope that my very first attempt to re-create something straight forward In Power BI isn't thwarted!

Also, any thoughts on the Closing vs Exit query?

Cheers

[jthomson]

So, in the report view, selecting the named days of the week column, bringing up the Modelling ribbon and hitting the "Sort by Column" option and then having it sort by your day number column doesn't do anything? You don't add it as a hidden field on the x-axis per se, you're effectively saying your day number is an index column to sort by. It's a fairly common issue that mostly comes up with wanting to sort months in the same way if you want to try searching for that as an alternative, it's the same principle

[MarkSL]

Hi, 

I have tried again, trying keep it as simple as possible and no, I can't get to sort as required.

Simple test data created and sorted by DayNo:

Line chart created over the above and X-Axis is alphabetical:

Thanks again for your help looking at this.

Mark

[Vvelarde · Accepted solution]

@MarkSL

Hi, select your column Day of Week after that go to Sort By Column ---Day No

Regards

Victor

Lima - Peru

[MarkSL]

Aha!  That did it.  Thank you both and I am sure that is what jthomson had been tell me to do too 🙂

Does seem slightly odd / unclear to me though?  Must be a technique which I need to learn...

Can someone finally clarify on my query regarding starting ones work again and wanting to 'Close' and abandon a dashboard without having to exit Power BI?