Nov 18 2017
01:27 PM
- last edited on
Jul 25 2018
10:26 AM
by
TechCommunityAP
Nov 18 2017
01:27 PM
- last edited on
Jul 25 2018
10:26 AM
by
TechCommunityAP
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?
Nov 18 2017 01:42 PM
Hi Morten,
That is "culture" parameter in function where applicable and locale in UI, default for workbook and/or for concrete steps, for example
Nov 18 2017 06:39 PM
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
Nov 20 2017 01:58 AM
SolutionHi 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.
Nov 20 2017 07:16 AM
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
Nov 20 2017 01:58 AM
SolutionHi 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.