Workflow table dates getting converted incorrectly on publishing to lakehouse

Hi jarmesb - This issue with dates getting formatted incorrectly upon publishing to the Lakehouse could stem from how the Lakehouse processes or stores dates. Here are some troubleshooting steps and solutions that might help:

Check for Inconsistencies in Date Types:

Even if the preview shows dates as expected, inconsistencies may arise in how dates are handled internally by the Lakehouse storage engine.
Before finalizing the workflow, ensure that dates are explicitly cast to a consistent format (e.g., DATE or DATETIME) across all transformation steps.
Try Using an Explicit Date Conversion Function:

In some systems, using functions like FORMAT() or CONVERT() to explicitly set the format can force the Lakehouse engine to interpret dates correctly. If you can, try casting or formatting dates right before writing them to the Lakehouse.
Investigate Locale/Regional Settings on the Data Source and Destination:

Even if there isn’t a direct setting for locale in the Lakehouse UI, it’s worth checking the locale settings in your environment (Power BI, Lakehouse configuration, or underlying SQL database settings if applicable).
Sometimes, the environment’s default locale setting can influence how dates are interpreted.
Test with Alternate Date Storage Formats:

Since you mentioned that exporting as text format works correctly, you might try storing dates as text (yyyy-MM-dd) instead of actual DATE or DATETIME types in the Lakehouse.
Once the data is in Lakehouse, you could add a calculated column that converts the text date back to a date format if needed.
Use a Dummy Date Table for Validation:

To confirm if the issue lies within the Lakehouse storage, try uploading a small dummy CSV with only date values in different formats to the Lakehouse.
If these dates transform correctly, the issue may be specific to how the Lakehouse processes data from your workflow.
Inspect Transformation Logic on Re-import:

After publishing, re-import the table from Lakehouse back into Power BI or another visualization tool. This can help identify if the issue is with Lakehouse’s storage or the process that publishes to it.
If none of these steps work, it may be beneficial to reach out to Lakehouse’s support or documentation to confirm if there are known limitations or specific steps for handling dates during data transformation and storage.