I have the following problem. I have a column called "StartDate". The column is a string, like "15.01.2025 07:28".

I wanted to convert this into a date. So I used this syntax:

= Table.TransformColumns(Source, 
   {"StartDate", each Date.FromText(Text.Range(_, 0, 10), [Format="dd.MM.yyyy"]), type date})

(as described here: )

But it didn’t work. I got the error message:

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

After some try and error I found out, that this works:

= Table.TransformColumns(Source, 
   {"StartDate", each Date.FromText(Text.Range(_, 0, 10), "de-DE"), type date})

But I don’t understand this. The manual clearly states there should be a record here not a string. Also this string only accepts Cultures, not format strings.

Why doesn’t it work currectly in Excel? And how could I use format string here?

I use Excel 2019 btw.

I have the following problem. I have a column called "StartDate". The column is a string, like "15.01.2025 07:28".

I wanted to convert this into a date. So I used this syntax:

= Table.TransformColumns(Source, 
   {"StartDate", each Date.FromText(Text.Range(_, 0, 10), [Format="dd.MM.yyyy"]), type date})

(as described here: https://learn.microsoft/en-us/powerquery-m/date-fromtext#example-4)

But it didn’t work. I got the error message:

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

After some try and error I found out, that this works:

= Table.TransformColumns(Source, 
   {"StartDate", each Date.FromText(Text.Range(_, 0, 10), "de-DE"), type date})

But I don’t understand this. The manual clearly states there should be a record here not a string. Also this string only accepts Cultures, not format strings.

Why doesn’t it work currectly in Excel? And how could I use format string here?

I use Excel 2019 btw.

• excel
• powerquery
• m
• excel-2019

• What are your Windows Regional Settings? Could it be that your year-month-day tokens are other than yMd? Also, the article you cite indicates that options can also be a text string "to support legacy workflows". But if you use a text string, you can only specify the culture, not the format. Your code does work as expected for me, too, using 365. – Ron Rosenfeld Commented Jan 30 at 21:47
• One other way to "track it down" might be to try: Table.TransformColumns(Source, {"StartDate", each Date.FromText(Text.Range(_, 0, 10), [Culture="de-DE"]), type date}). If that doesn't work, then probably your version of that PQ function does not support the options record. If it does work, then possibly your year-month-day tokens are different than what you are using. – Ron Rosenfeld Commented Jan 31 at 0:41
• I tried different variants. The problem is, that the record, meaning values inside brackets, are not accepted. Is has nothing to do, with my regional settings. (e. g. [Culture="de-DE"] also doesn’t work.) – Martini Bianco Commented Jan 31 at 11:11
• 1 As I wrote, since [Culture="de-DE"] doesn't work, the logical conclusion is that the version of PQ packaged with 2019 does not include the Options record but rather is considered a legacy workflow. I do not believe you can use the format string with this function unless you upgrade your Excel or switch to Power BI. – Ron Rosenfeld Commented Jan 31 at 12:10

1 Answer 1

It is probably your older version of Excel. In Excel 365, your first code works perfectly.