simple text to date

[KBD]

Hello All:

I have a simple issue, but yet I cannot crack it. 🤔

Have a CSV file with a couple of date and date/time attributes.

Therefore they need to be converted to Date and Date/Time.

Could not figure out how to upload the CSV file.

Can we upload files?

In Power Query in an Azure Data Factory the CSV file loads fine

The souce data (INSTALLDATE) seems to have loaded fine and my create a new attribute (InstallD) seems to have worked.

I used "Add Column--> Custom Col"  to create the col.

But I geetting an error message:

UserQuery : Expression.Error: The Power Query Spark Runtime does not support the function (collection as any, fieldName as any) as any => (...) .

Cannot Validate   

Do not have a clue why this went sideways.

Your kind assistance is requested.

Best Regards

KD

[Anonymous]

Hi @KBD ,

I did some test myself in Dataflow Gen 2 but there is no problem:

May I ask what your specific test environment is?

BTW I found this in the official docs, maybe it will help you:
https://learn.microsoft.com/en-us/azure/data-factory/wrangling-functions#formatting-datetime-columns 

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KBD]

I am working in Azure in a Pipeline, in a PowerQuery step.

I did my conversion over again.  No progress.

Can somebody tell me exactly what the ExpressionError means?

Read through this:  Data wrangling functions in Azure Data Factory - Azure Data Factory | Microsoft Learn

Noticed this:

Table.TransformColumnTypes

This is supported in most cases. The following scenarios are unsupported: transforming string to currency type, transforming string to time type, transforming string to Percentage type and tranfoming with locale.

Above it says transforming string to time type is NOT supported.   Maybe string to date is not supported.

Your kind assistance is requested.

KD

[Anonymous]

Just go into the formula bar and update the code as above. You are using a type enumeration where a function is required.

--Nate

[Anonymous]

Yeah. Two things. Table.TransformColumnTypes needs a function, like Date.From, not "type date". But instead of using Table.TransformColumnTypes, you can just add type date as the last parameter to the Table.AddColumn function, like

["en-us"]), type datetime)

--Nate

[KBD]

This is what I specified in the Custom Com diolog

shows this in the code window:

Table.TransformColumnTypes(Table.AddColumn(Source, "InstallD", each DateTime.FromText([INSTALLDATE], [Format = "yyyy-MM-dd", Culture = "en-us"])), {{"InstallD", type date}})

Will not validate.

Error message:

UserQuery : Expression.Error: The Power Query Spark Runtime does not support the function (collection as any, fieldName as any) as any => (...) .

[KBD]

Please note I am not writing the code, Custom Col dialog is generating the code.

KD

[Anonymous]

Have you tried just using the Get data dialogue and choosing text file?

--Nate

[KBD]

Hello:

Ripped out the steps, basically started over.  

PQ is calic'ing the new col and the data looks correct.

Code looks like this:

Table.TransformColumnTypes(Table.AddColumn(Source, "InstallD", each DateTime.FromText([INSTALLDATE], [Format = "yyyy-MM-dd", Culture = "en-us"])), {{"InstallD", type date}})

Will not validate.

Error message:

UserQuery : Expression.Error: The Power Query Spark Runtime does not support the function (collection as any, fieldName as any) as any => (...) .

[KBD]

Are you repling to the correct quetion?

The issue here is simple text to date converion.

KD