In an Excel table I have a date field in A2 that is 5/12/16 and if I want to convert that in a new column to mmm-dd I use the formula =TEXT(A2, "mmm-dd") and then I get Dec-05 and this I then use in a daily slicer. I have played around with a variety of options and googled to no avail - what is the correct formula equivalent in Power Query?
Date operations can be tricky, so I think it's not enough to just say that you have 5/12/16 in a cell, this can mean that you have a text in that cell. Is in that cell a date or a text that looks like a date?
If it's a text, then you have to reformat that text, in order to convert to date without errors:
=Text.Start([DateText],6) & "20" & Text.End([DateText],2). Convert this column to date format.
Then, convert this date to text, by adding a column wih this formula:
= Date.ToText([Date],"MMM-yy")
If you already have a date, not text, use just the last formula.
Thank you Catalin and you're right I should have been clearer in my post that my date field was already formatted as a date and not as text!
Your formula works exactly as I needed once I replaced the -yy with -dd. I really appreciate your help
PS I have also made a note of the formula to convert dates from text formatting to date formatting - I am sure that at some point it the future this will be very useful as well!
The first formula can also work , even if you have a date in the source table. If you don't convert that column to date type in Power Query, it will be text, and it can be processed with text formulas, that's good to know.
