topic Re: Date Display Formats in Power Query

Date Display Formats

dglsmason — Wed, 05 Aug 2015 18:56:40 GMT

Using Power BI Desktop to create reports and charts. What is the syntax for displaying dates in specific formats. For example, "01/25/2015" instead of "Monday, January 25, 2015". Etc. Thanks!

Re: Date Display Formats

andre — Wed, 05 Aug 2015 19:39:32 GMT

click on the time field in your model then you can click on Modeling in the Desktop ribbon and pick the desired date format

Re: Date Display Formats

Pieter — Tue, 10 Jan 2017 16:20:33 GMT

This does not hold true for the Power BI browser application. If you change the format to date the date is displayed as d-m-yyyy in the Power BI desktop, but the date is displayed as Monday 1 January 2016 in a graph in your browser.

Re: Date Display Formats

Anonymous — Tue, 10 Jan 2017 16:32:37 GMT

Pieter Do you have a screenshot that includes your visual and fields you are using? The format carries through to the Service with no issues on any visual for my tests.

Re: Date Display Formats

Pieter — Tue, 10 Jan 2017 16:40:24 GMT

Thanks for the quick reply!

A screenshot, sure. "Week van inschrijving" has the format Date in Power BI desktop. The screenshot is from the webapp.

Re: Date Display Formats

Pieter — Tue, 10 Jan 2017 16:44:31 GMT

Like this:

Re: Date Display Formats

Anonymous — Tue, 10 Jan 2017 17:07:52 GMT

Pieter Your screen grab shows the "Query Editor" - after you "close & apply", click on "Modeling" in the header. Under the "Formatting" header do you have "Data Type: Date" and a selected format?

This is the model layer, you need to explicitly define the format there as well.

Re: Date Display Formats

Pieter — Wed, 11 Jan 2017 09:01:12 GMT

Thanks a lot Anonymous, that helped.

Not very intuitive though.

Re: Date Display Formats

Anonymous — Wed, 11 Jan 2017 13:49:17 GMT

Pieter Think of the Desktop as 2 different environments. When you load data you are using "Power Query" to do the ETL. Then after you "Close & Apply" the data is loaded into a Tabular Model. Much in the same way you would do checks and format things in the Tabular model, you still need to verify and validate correct format there as well.

Glad it works.

Re: Date Display Formats

jj14x — Fri, 17 Feb 2017 20:01:26 GMT

Is there a way to set the default date format in the desktop application?

In Power Query, I can select all the date fields at one time and apply the date format to all together.

In the Desktop application however, I can only select one column at a time, change it, (which will make the view shift all the way to the first colum), scroll over to the next column, select, change it, and repeat.

Very tedious for some of the larger datasets that are filled with date columns :)

Re: Date Display Formats

Anonymous — Fri, 17 Feb 2017 21:51:42 GMT

jj14x I'm not aware of the way in which you could manually update the M code, but there is most certianly a way in the Advanced Editor. Otherwise, if you are stuck to the UI, a quicker way to get all the columns at one time is to select a column and CTRL + Click any additional columns that will be changed to the same format. All of them will highlight and you can then select your format.

Re: Date Display Formats

jj14x — Fri, 17 Feb 2017 23:12:23 GMT

Thanks Eno1978. CTRL + Click works in the Power Query window, but for some reason, doesn't work in the Power BI desktop application. The latter forces me to select one column at a time.

Advanced editor would be in Power Query as well, right? I changed the date type in Power Query - that was easy enough. But when I return to Power BI, the date format there defaults to having the weekday before the actual date.

(I am new to Power Query/Power BI, so may be overlooking something obvious)

Re: Date Display Formats

Anonymous — Sun, 19 Feb 2017 15:41:34 GMT

jj14x If you are out of the Advanced Editor and working in the front end, then you have availability to expand the tables on the right hand side, and while you can't perform the CTRL+Click, you can navigate much easier selecting the columns from that area. The main data page will jump to the column you select.

Re: Date Display Formats

jj14x — Sun, 19 Feb 2017 16:07:38 GMT

Thanks! That's a good idea.

Re: Date Display Formats

JoshSiit — Tue, 20 Feb 2018 15:29:56 GMT

We are having similar problems. There is no option for formatting English dates (dd/mm/yyyy) in the moddelling section of the ribbon..

Does anyone know how to get dates to format like this?

All our slicers are showing dates in US format where as in the tables, the dates are in UK format.

Any help would be appreciated.

Thanks

Josh

Re: Date Display Formats

XLBob — Fri, 23 Feb 2018 05:41:49 GMT

JoshSiit I am having the same issue and there is no option under the modelling tab that can let me format the date as dd-mm-yy, the closest I can find in the format is dd-mmm-yy. what's even worse is that the dates looks fine in data view but in the reports view it has time after the dates. big sigh here

Re: Date Display Formats

Anonymous — Tue, 13 Mar 2018 12:58:13 GMT

I am getting the same as XLBob. no option for dd/MM/yyyy only dd/MMM/yyyy and for some strange reason it still shows as American format on the sliders.

Re: Date Display Formats

XLBob — Wed, 14 Mar 2018 10:11:35 GMT

Anonymous have you updated to the latest version? the issue went away after upgrade

Re: Date Display Formats

Anonymous — Wed, 14 Mar 2018 10:31:55 GMT

Hi XLBob

I am currently on Version: 2.55.5010.641 64-bit (February 2018)

which Version are you using?

Re: Date Display Formats

JoshSiit — Wed, 14 Mar 2018 10:33:52 GMT

I've realised it was a problem with the February update. So I rolled back to the previous build and the problem went away.