Power Query Date To Text

[wsnyder]

Hi all,
I am trying to convert a date to text in the format yyyymmdd.

If I enter = Date.ToText([RefDate]) I am able to convert a date to text.

But when I enter
Date.ToText([RefDate], [Format="yyyymmdd"])

I get an error:

Expression.Error: We cannot convert a value of type Record to type Text.
Details: Value= Format=yyyymmdd Type=[Type]

What am I doing wrong?
Thanks
-w

 

[johnny51981]

You have to break it up into pieces, concatenate it, and then bring it together to convert to text.

Give this a shot:
Date.ToText(date#(Date.Year([RefDate])&","&Date.Month([RefDate])&","&Date.Day([RefDate)),[Format="yyyymmdd"])

 

[avicric]

Hi all,
I am trying to convert a date to text in the format yyyymmdd.

If I enter = Date.ToText([RefDate]) I am able to convert a date to text.

But when I enter
Date.ToText([RefDate], [Format="yyyymmdd"])

I get an error:



What am I doing wrong?
Thanks
-w

Hello wsnyder,
Check the date column if there are any cells which have datetime format.....

 

[Rickmaurinus]

Hey Wsnyder,


There are a total of 17 format string options linked with the Date.ToText function. The interesting part is that you can combine these pieces in various ways to form a custom date string. For instance, using a particular arrangement yields the result "Sun December 31, 2023."

Date.ToText(
  #date( 2023, 12, 31 ),
  [Format="ddd MMMM d, yyyy"]
)

And this next string returns us 'november':

Date.ToText(
  #date( 2023, 11 5 ),
  [Format="MMMM", Culture="nl-NL"]
)

 

[RoryA]

Since this has been resurrected, the issue with the original formula was that the 'mm' in the string means 'minutes' not 'months', which is 'MM' (M code being case sensitive). The error message is misleading (it seems to be better these days). So this:

Date.ToText([RefDate], [Format="yyyymmdd"])

just needs to be:

Date.ToText([RefDate], [Format="yyyyMMdd"])

 

[Rickmaurinus]

Since this has been resurrected, the issue with the original formula was that the 'mm' in the string means 'minutes' not 'months', which is 'MM' (M code being case sensitive). The error message is misleading (it seems to be better these days). So this:

Date.ToText([RefDate], [Format="yyyymmdd"])

just needs to be:

Date.ToText([RefDate], [Format="yyyyMMdd"])

Good point, it can be really confusing since yyyy and dd are accepted lowercase, where the M should be uppercase. I've found more of these for Time, DateTime and DateTimeZone confusing. You can find the different scenarios with examples written out clearly in this picture:

Date.ToText - Function | Power Query How

Date.ToText is a Power Query M function that returns a textual representation of date with optional formatting and culture parameters.

powerquery.how

Cheers,
Rick

 

[GraH]

O my gorilla, @Rickmaurinus is in the house.