Power Query - How to Format Date Column to Specific Format?

[rodneyc8063]

I have a bit of a quick question I was hoping for some help

In Power Query I have a .csv that I have downloaded

One of the columns is set as a "Text" data type when its clearly a date column in the format of YYYY-MM-DD.

After I modified the column data type to date, I was then trying to figure out how I could customize or change the format of the date column.

From what I found I can either

1) Split/Merge the column based on a delimited - Which I find personally very odd, as in PBI desktop itself its pretty much click-click-click and I can change the format of the column without needing to do any extra split/merge

2) Change the locale

I tried option 2 to change the locale, but for some reason the column is being stubborn and still staying its its original format of YYYY-MM-DD regardless of what ever locale I choose.

What am I missing?

I would have thought being able to change the date format in Power Query would be trivial so not sure if I am missing something super small 😞

Any insight would be much appreciated.

[v-alq-msft]

Hi, @rodneyc8063 

About data format in Power BI, I'd like to suggest you refer to the following document for further help.

Power BI Date Formatting 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@rodneyc8063 in your powerquery window, you can create a new column from your date column. You select the date column and then go to File->Column from example -> from selection

then in the custom column type first 2-3 values as above then power query automatically will convert values for you.

[rodneyc8063]

@negi007 

Thanks for this tip!

I was just curious and hoping for something as easy as the front end PBI desktop where I could simply "change" the format of the data column.

I thought the same or very similar functionality should exist in both PBI desktop and Power Query and was just confused why it wasnt easier to change the format of a date column in Power Query as its a pretty common thing that needs to be done.

[amitchandak]

@rodneyc8063 , Any specific need to format in power query. Because you have format option if power bi column tools for display purpose, outside power query, you can use that.

[rodneyc8063]

@amitchandak- Thats a good question

So to be honest I am not an every day user of PBI and only help out when I am asked to, so I am still slowly learning the tool.

I do realize, as you pointed out that in the display portion of PBI Desktop that it is very easy to just simply change the date format. Thats why I was just "curious" and "assumed" that some similar easy functionality would be in Power Query as well.

I guess one of my concerns is consistency. As in the future once this dataset is built out, I am assuming a number of other users could use the same data set for different reports. So I just wanted to make sure that the data is properly formatted and set, before letting folks come in and start using the data set as is.

I guess its not a huge concern as users can still format it in their report themselves, but it would just be nice to have everything set up from the get go