My work is connected with data coming from different countries, which have different local formats of date, numbers etc.
To be able to consolidate data in Excel models, I have to convert data accordingly.
In Power Query and Get & Transform we can set default Locale in Query Options
However, when I load data from different sources and apply Changed Type ( Table.TransformColumnTypes ) operation I must use corresponding locale for argument “culture”, which is optional.
I noticed interesting behavior. When I change type
New step “Changed Type” appears in Applied Steps without gear
Of course, when Power Query engine created this step it used default Regional Locale, which is “en-US”, as a result values are wrong. I have to use Russian locale for this sample.
When I manually add argument “culture” in formula bar – gear appears near the “Changed Type” step:
It shows nice menu – Change Type with Locale
It even shows sample format when I select Locale
For me it is fine to add locale manually, but when I explained this to my colleagues they were not happy to hear this.
Maybe I don’t know something… But how to call this “Change Type with Locale” by default?
E.g. when I change Data Type – ask about locale I want to use.
Probably, worth to add something like checkbox “Always ask about locale when I change data type” in the area of Current Workbook options.
Or just display gear for step Changed Type by default to let user go the and select local from list.
There is also an idea with close meaning on Power BI uservoice forum
The option was found. Thanks to Frank and to Maxim Zelensky (@Hohlick)
To be honest, I’ve never used this menu to change type. Much faster was to change it from ribbon and from new small button in column header, and then manually add locale.
Good to know that at least option exists, although is very hidden :-(.
2 thoughts on “Changed Type step in Power Query”
you can change the column type in the context menu where you can choose the locale at the bottom.
Thanks Frank! Just got the same advice on Twitter :). Amazed how fast is community!
Seems this option is for real geeks, as it is placed so deeply.