SOLVED

Convert input decimal separator from API source to non-English

Copper Contributor

I'm using APIs (PowerQuery, Excel 2016) to get financial data from web that returns decimal separator as period ("."), but I need to convert it to non-English localization comma (","), which is my system default and output requirement.

 

API input from web is in sheet A.

How do I convert input "." to output "," in the destination cell in sheet B where I have link to source in sheet A?

4 Replies

Hi Sergei,

 

I don't see how this applies to API-source from web, as it utilizes a static CSV-source/text files, while I ask for dynamic API source.

 

I don't see how I can change the decimal from the source/result - here is an example:

https://api.coinmarketcap.com/v1/ticker/bitcoin/

 

It returns Column1.price_usd which I need to have a different separator.

 

 

M

 

 

best response confirmed by Morten Ross (Copper Contributor)
Solution

Hi Morten,

 

That doesn't matter which connector you use. After Power Query returns the table remove Change Type step if it was added automatically. Your column(s) will be in type Any. Now Change Type using locale, select Data Type as decimal number and source data locale (English US or like). It converts the value from Any to number and from US locale to your home locale (French). Numbers will be shown in you home locale, i.e. with comma as digital separator.

ChangeTypeUsingLocale.JPG

 

Hi Sergei,

 

My bad - I selected destination locale and not source locale, as my brain's logic was to assign what I wanted to convert it TO.

 

Many thanks for your persistence Sergei. Fortunately I found coincap.io which is in "correct" locale in the meantime, but this has provided a valuable freedom of choice for future sources.

 

M

 

 

1 best response

Accepted Solutions
best response confirmed by Morten Ross (Copper Contributor)
Solution

Hi Morten,

 

That doesn't matter which connector you use. After Power Query returns the table remove Change Type step if it was added automatically. Your column(s) will be in type Any. Now Change Type using locale, select Data Type as decimal number and source data locale (English US or like). It converts the value from Any to number and from US locale to your home locale (French). Numbers will be shown in you home locale, i.e. with comma as digital separator.

ChangeTypeUsingLocale.JPG

 

View solution in original post