Forum Discussion
simple text to date
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
9 Replies
Are you repling to the correct quetion?
The issue here is simple text to date converion.
KD
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 => (...) .
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 => (...) .Please note I am not writing the code, Custom Col dialog is generating the code.
KD
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.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
