Sharepoint Date Format - Regional Settings - Locale issue

[Anonymous]

Hello

I want to create two Queries in the same workbook where one query links to a Sharepoint with US locale (date/region format) and the other to a Sharepoint with EU locale (date/region format).

The problem is that I need to specify the right locate/regional settings in the Query Options for it to work and not display Error in the Date columns.

However, this option seems to apply to both/all queries so when one query works the other doesn't.

I tried transforming the data to text, I tried changing the 'Using locale' option in the column and I tried DateTime.ToText in a new column but it does not work.

Any idea?

[v-yingjl]

Hi @Anonymous ,

See:

Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table

You can use the specified culture in the optional parameter culture (for example, "en-US"). 

In this issue, if the default type of EU date is text, you can try like this:

= Table.TransformColumnTypes(#"Changed Type", {{"Column3", type date}}, "en-GB")

Refer:

1. Table.TransformColumnTypes()
2. Flawless Date Conversion in Power Query 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yingjl  thanks I will try that although to be honest I saw several posts of this and the conclusion seems to be that it's a bug of the Api 15 (it works with Api 14).

[mahoneypat]

You can switch your datetime columns to datetimezone type and use DateTimeZone.SwitchZone to match them up.

Pat