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
Well doesnt work for me when I use DirectQuery with Redshift.
RE: Power Query, convert date fields to monthname, weekdayname, quartername
Is there a plan rather than just creating text fields out of dates, format the dates with custom formats, so they are still sortable as a date/ time field. For example would like to format the field as "ddd dd mmm h:mm AM/PM", which is possible in excel. This may be wrong discussion thread but other comments have indicated the same kind of issue, Thanks
RE: Power Query, convert date fields to monthname, weekdayname, quartername
Hello, how can we sort the Months chronologically by name not by number? Can you please assist?
RE: Power Query, convert date fields to monthname, weekdayname, quartername
I have a 7 digit julian date like 2456839 ....how easy is to covert that in Sydney time (Australia) using Power Bi Desktop?
RE: Power Query, convert date fields to monthname, weekdayname, quartername
This feature does not work in Directquery mode. Will there be an update to allow it?
RE: Power Query, convert date fields to monthname, weekdayname, quartername
If you want to sort by month in the slicer you can try this
Month = MONTH([date]) &"-"& format([date],"mmmm")
Will give you 1-January etc.
RE: Power Query, convert date fields to monthname, weekdayname, quartername
you can access weekday's name in query editor environment, then using Date, Day, Name of the day
RE: Power Query, convert date fields to monthname, weekdayname, quartername
Where exactly is this functionality? I just downloaded the latest version and the weekday formula still returns a number and not the name.
RE: Power Query, convert date fields to monthname, weekdayname, quartername
Hi Admin,
thanks for the update,it's very handy, is there any chance to sort the months name by calendar not alphabetically?
in particular, I need that when I use Slicer.
RE: Power Query, convert date fields to monthname, weekdayname, quartername
'use this to just show month name of date data
=FORMAT([date to format], "mmmm").
But if you do this, you can't order it by month.
If you try to order it, i'll be orderd alphabetically.
I hope, next version of power BI will accomodate Month Data with sort capability.