Azure Data Factory Power Query Date Source not compatible with SQL

Question

question

DAnnaTeresa-3636 asked Apr 6, '22 KranthiPakala-MSFT commented Apr 8, '22

Azure Data Factory Power Query Date Source not compatible with SQL

Hello,

My source file effective date column has a year of 4712 that causes errors when trying to import with a simple copy job in Azure SQL Server. I decided to try to use Power Query in ADF to transform the date, however the column also has nulls which is making things complicated.

Source File Date Column example (string):
12/31/4712 00:00:00
(blank)
2/5/2023 00:00:00

Transformation:

let Source = #"LoadHCMEmp_SourceDS",
#"Replaced value" = Table.ReplaceValue(Source, "12/31/4712 00:00:00", "12/31/2050", Replacer.ReplaceText, {"Effective_End_Date"}),
#"Replaced value 1" = Table.ReplaceValue(#"Replaced value", "", "12/31/2050", Replacer.ReplaceValue, {"Effective_End_Date"}),
#"Replaced value 2" = Table.ReplaceValue(#"Replaced value 1", " 00:00:00", "", Replacer.ReplaceText, {"Effective_End_Date"}) in #"Replaced value 2"

The problem is when I transform the above into a date, I get:
UserQuery: Expression.Error: Unsupported constant null.

I've tried to import into sql using the pipeline both as a string and as a date (after the above adjustments). When I import as a date, it errors out (see above). When I import after the above transformations, the column comes through as blank. A simple copy job doesn't work because of the year issue.

To push the data into sql we're using the Sink in the Power Query:
Settings -> Allow insert (checkbox checked)
Settings -> Truncate table

Source file is csv. Sink is Azure SQL Server.

Essentially I just want to be able to import the dates, whether that means transforming everything thats super far off to 2050 and making the nulls 12/31/2050 or keeping them as null

azure-data-factory power-query-not-supported

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DAnnaTeresa-3636 · Apr 08 at 08:13 PM

@KranthiPakala-MSFT - Thanks for looking into this issue.
1) Is your sink date column type of null or not null? - Its is NULL
2) Could you please try to transform the date column values from null/blank to some default values as described in this video and see if that helps
I looked at the video, this what is happening in the below step already:

 #"Replaced value 1" = Table.ReplaceValue(#"Replaced value", "", "12/31/2050", Replacer.ReplaceValue, {"Effective_End_Date"}),

The below works perfectly in power bi query studio however does not work in Azure Data Factory (I've done this many times there). I posted the issue here since it really seems to be a problem with the instance in Data Factory.

 let Source = #"LoadHCMEmp_SourceDS",
 #"Replaced value" = Table.ReplaceValue(Source, "12/31/4712 00:00:00", "12/31/2050", Replacer.ReplaceText, {"Effective_End_Date"}),
 #"Replaced value 1" = Table.ReplaceValue(#"Replaced value", "", "12/31/2050", Replacer.ReplaceValue, {"Effective_End_Date"}),
 #"Replaced value 2" = Table.ReplaceValue(#"Replaced value 1", " 00:00:00", "", Replacer.ReplaceText, {"Effective_End_Date"}) in #"Replaced value 2"

1 ·

KranthiPakala-MSFT DAnnaTeresa-3636 · Apr 08 at 11:35 PM

Hello @DAnnaTeresa-3636,

Thanks for sharing additional context of the issue. I will further investigate on this issue and will get back to you as soon as my findings ready.

0 ·

KranthiPakala-MSFT · Apr 08 at 07:51 PM

Hello @DAnnaTeresa-3636,

Thanks for the question and using MS Q&A platform.

My understanding of the ask here is that your source file effective data column holds year in it and you are receiving some future year (far off dates) (for eg - yyyy - 4712), and also there are few null records from source because of which things are getting complicated while copy job is running. Hence you tried with Power query activity in ADF to transform the data column but even though you are seeing issues with null records in that column. Please correct if I'm not accurate.

To better assist on this query could you please confirm below :

Is your sink date column type of null or not null? If it is not null will it be possible to update it null as your source contains nulls and also it will make things simple.
Could you please try to transform the date column values from null/blank to some default values as described in this video and see if that helps - Fill down and replace null/blank values in Power Query

Since the issue is more of Powery query related, I would recommend to also reach out in Power query community which is dedicated community forum for Power BI services - Microsoft Power BI community

Do let us know how it goes.

Thank you

0 ·

question