Forum Discussion

rodneyc8063_1's avatar
rodneyc8063_1
Helper V
5 years ago

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

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.

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    rodneyc8063_1 , 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_1's avatar
      rodneyc8063_1
      Helper V
      5 years ago

      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

  • negi007's avatar
    negi007
    Community Champion
    5 years ago

    rodneyc8063_1 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_1's avatar
      rodneyc8063_1
      Helper V
      5 years ago

      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.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, rodneyc8063_1 

     

    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.