CODEWITHSUNDEEP
excelpowerquerymexcel-2019
Martini Bianco
Martini Bianco

Reputation: 1795

Date.FromText in Excel Power Query Editor is not working

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.com/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.

Upvotes: 1

Views: 41

Answers (1)

davidebacci
davidebacci

Reputation: 30174

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

enter image description here

Upvotes: 1

Related Questions