I'm doing similar operations to the case reported here - https://community.powerbi.com/t5/Service/Specified-culture-is-not-supported-error/td-p/8730 - but the solution there is not working for me currently.
I have made a test with the information you provide by entering data and edit in Editor Queries, publish the report to Power BI Service and have a refresh, everything works well.
For another way, I configured an on-premises gateway and refresh the dataset, it also refresh successfully without any error.
Do you have this error when you configure a gateway? If it is, could you share more details of the error?
Could you share a dummy pbix file which can reproduce this issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Here is a sanitized test that repro's the problem.
The source dataset is a JSON file, with a subset of actual data from the production system as returned by the provider system and intermediary proxy. The date column strings are in dd/mm/yyyy format.
The pbix will initially publish this dataset correctly without errors, but a refresh with a gateway configured for the JSON file will throw the error. Removing the Changed Type with Locale steps from the 'locale-bug' table will let the dataset refresh online correctly.
I need to be able to do date-based analysis of the rows in the primary table. I do control the source for the intermediary proxy, so I can transform the date strings from dd/mm/yyyy to mm/dd/yyyy to make it PBI friendly, but I would prefer to not to have to do that, and just use the pass-through data as-is without any mid-stream modification.
For reference, I have the following columns in the primary table from the provider:
I need to do filters and aggregation on those values, and derive additional values of the difference of (3 - 1) and (2 - 1). I could potentially derive the string columns from the source table, convert the types in derived tables, then link them back in a relationship on the row's primary unique ID. That's messy as well, which makes risks of problems.
After I originally made this post, it disappeared for me for some reason, so there's a duplicate post at https://community.powerbi.com/t5/Service/The-specified-culture-is-not-supported-on-an-extracted-date..., and an issue report at https://community.powerbi.com/t5/Issues/App-Dataset-refresh-fails-with-a-table-derived-from-a-date/i...