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
Hello
@D'Anna, Teresa
,
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
@KranthiPakala-MSFT
- Thanks for looking into this issue.
Is your sink date column type of null or not null? - Its is NULL
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"
