Power BI
CompletedPower Query, convert date fields to monthname, weekdayname, quartername
Terje Fjeldstad on 08 May 2015 00:21:57
When using power Query to add columns based on A date field, it would be Nice to also be able to convert to month name, not just month number. Example; 31.01.2012 should have built-in function to set december or dec....monday or mon. Till now, we can only get numeric values.
Administrator on 02 Jul 2016 00:11:43
I'm glad to report that this transformation is now available in the June Update of Power BI Desktop: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-june-update/ Extract Week Day and Month Name from a DateTime column Another very common customer request has been to provide a built-in transformation to extract the WeekDay or Month names from a given Date/Time column. We’ve made this available from the Query Editor ribbon (either via Transform to modify an existing column, or via Add Column to insert a new one). These two options can be found under “Date -> Day -> Name of Day” and “Date -> Month -> Name of Month”, respectively. Note that the Day/Month names are localized according to the Locale setting for your Power BI Desktop file (available under File -> Options -> Current File -> Regional Settings).
- Comments (23)
RE: Power Query, convert date fields to monthname, weekdayname, quartername
'Adding the equivalent TEXT function in Excel would be even better:
=TEXT(A1,"mmmm")
Gives
A1: 1
B2: "January"
RE: Power Query, convert date fields to monthname, weekdayname, quartername
When you extract month from a date, it'd be great if it can extract it in "January, February" etc, rather than just the number.
RE: Power Query, convert date fields to monthname, weekdayname, quartername
It is very bad. we need option to order the date. my requirement is order the data based on month.
RE: Power Query, convert date fields to monthname, weekdayname, quartername
Agree - Power BI's default date and time formatting is actually worse and offers fewer options than Excel 2007... and surely must be a drain on memory as you have to do a lot of data shaping just to get your time dimensions into shape - obviously there's the time series date hierarchy which is useful, but more often than not you want to plot / filter by specific names - end users like broad strokes.
RE: Power Query, convert date fields to monthname, weekdayname, quartername
Add a column =wekday([date])
Create a table (1..7)-(sun...sat)
Create relationship
RE: Power Query, convert date fields to monthname, weekdayname, quartername
It is not working, with Format function. I have pulled the data from SharePoint. While trying to get the month name from Created field. For July it gives the month name as January while using the Format function.
RE: Power Query, convert date fields to monthname, weekdayname, quartername
If you do =FORMAT([date],"MMM")
how do you prevent Power BI visualizations from screwing up the sort?
RE: Power Query, convert date fields to monthname, weekdayname, quartername
This is essential
The format date is not suitable for the reason given
RE: Power Query, convert date fields to monthname, weekdayname, quartername
You can use the formula =FORMAT([date],"MMM") that Luis suggested then sort it by =Month([date]). To sort it by a separate column you can use the "Sort By Column" in the data tab.
RE: Power Query, convert date fields to monthname, weekdayname, quartername
'@dhoff can you please help where to add this formula