Introduction
If you are Azure Data Factory (ADF) to work with Azure Synapse Link and Dataverse Data, (I’ve blogged about it here) ensuring data accuracy and consistency can be challenging. In this blog post, we will address a common issue faced by many users: NULL values in date columns. I’ll guide you through the process of resolving this issue step by step.
The Solution
As a consultant you might know it. You get a requirement and you stuble from one pitfall into the next one. First, I received a lot of Server is busy Errors (503) (I’ve blogged here about it), then I’ve created a Azure Data Factory to tackle the data source appears to have changed
issue and I’ve now lost access to my date columns. In my case I’ve head a beautiful common data service inline dataset ready to use filled with date columns and I was stuck with this litte error. But why? This issue arises because Azure Data Factory may fail to correctly convert the date columns into the desired format.
1. Understand Your Data:
Before making any changes, it’s crucial to understand your data thoroughly. Date columns can be tricky, especially if they have values in multiple formats or if the format isn’t consistent across records. Take time to examine your data to identify the different formats and variations. Especially if you are working with Dynamics CRM, you might receive multiple formats since the users locale will always dictate the timestamp thats saved to your record. Be aware of that.
2. Adjust the Schema:
To resolve the NULL value issue, you’ll need to adjust the schema of the date columns in your Azure Data Factory pipeline in the Source step. Here’s how:
In the Projection Step you find the Schema Options. You should use the “Allow Schema drift” as well as “Infer drifted column types” options. Inside of the Format area you can tick all the Date and Time Formats that might be relevant. If you are not sure, you can even tick all of them.
3. Validate your changes:
Before finalizing your adjustments, use the Data Preview feature to validate your transformations. It will take a while to boot up. This step is essential to ensure that the date columns now display the correct values and do not have NULL values.
Once you’ve successfully adjusted the date columns, continue with your data transformations as needed. Finally, I could save the transformed data to a .json file in Azure Data Lake Gen2 and consume it with my PowerBi Dataflow.
Conclusion:
By following these steps, you can overcome the challenge of NULL values in date columns when using Azure Data Factory to transform your Azure Synapse Link Data. Understanding your data, adjusting the schema, and testing your transformations are crucial aspects of this process. Once resolved, your data will be ready for efficient consumption in Power BI, empowering you to make data-driven decisions with confidence.