Date Data errors when using different people from different countries in Power Query

0
votes

I have developed a Excel based tool by using Power Query and it will be shared with people globally who will be using it. However I am facing a major problem when it comes to Date format as most of my formulas in power query are calculated based on the date column.

Example: India is using DD/MM/YYYY format;
         United Kingdom is using MM/DD/YYYY format
         Austria is using YYYY/DD/MM format
         China is using YYYY/MM/DD format

If I share this tool to all these people it is going to work only for one of them and other 3, it will show "Error" in date column.

Is there a way where I can change the date column to their Local Operating system reginal setting format? or Is there any other better way to fix this? I have tried various options and nothing worked for the given scenario.

I have also tried using Locale option and again it will work only for one scenario. enter image description here

1
excelpowerbipowerquery
You need to be able to identify which line comes from or is assigned to each region. Use a column with an IF statement to convert each region into ISO standard dates, then it should use locale when used in the browser – Jon
Thanks Jon, however I figured out a simple way of doing it. – user3186707

1 Answers

1
votes

I figured out a way to fix it. I have used the following formula =Date.From([Date Column],"en-GB") in a custom new column of power query which converts the date format into their Operating System Local Regional format setting and it is working as expected.

I tried with couple of examples and it is working fine.