topic Format number not recognized in Power Query

Format number not recognized

elokapo — Fri, 30 Jun 2023 18:18:03 GMT

Hi,

When trying to load an excel file with a column (Fee) having numbers, POwerBI Query does not recognize it as Decimal Number.

It gives for example 123.123.125.12 while I am expecting 123123125.12

It put extra dots (.) while the source (xls file) is showing a correct number format [$-de-CH,1]# ##0.00;(# ##0.00)

Anyone would have a tip to resolve this issue?

Thx,

Re: Format number not recognized

PhilipTreacy — Sun, 02 Jul 2023 00:47:14 GMT

elokapo

If PQ is doing an automated Change Type step on that coumn, delete it and try manually converting using Change Type using Locale

Looks like you are using a German (Swiss) format?

Regards

Phil

Re: Format number not recognized

elokapo — Mon, 03 Jul 2023 08:15:44 GMT

Hi,

I take those xls files from a folder. Those are the steps I have : (I have no Change Type)

The data comes directly like that:

Thx,

Re: Format number not recognized

elokapo — Mon, 03 Jul 2023 08:56:24 GMT

Here is what I have when loading the files:

Re: Format number not recognized

PhilipTreacy — Tue, 04 Jul 2023 02:45:57 GMT

elokapo

Can you please supply one/some of the XLS files that you are having the issue with, and your query to import them. It'll be easier to diagnose when I can test the data myself.

Regards

Phil

Re: Format number not recognized

elokapo — Tue, 04 Jul 2023 07:04:38 GMT

Hi,

thanks for your reply. PLease find THERE the requested files.

Below the settings for this file:

BR,

Re: Format number not recognized

PhilipTreacy — Wed, 05 Jul 2023 01:27:10 GMT

elokapo

Download my example PBIX file

In the sample file you gave me I can't reproduce your problem.

You said that the Fee column is giving issues but from your file it looks like it's the Value column as it has values greater than 1000? The Fee column in your file only has numbers like 12.34

Anyway, looking at the XLS file (why are you using XLS and not XLSX?) the numbers are formatted correctly. Importing these with Power Query shouldn't pose an issue.

You show that your default regional setting is EN(US), so is mine so we are both using the same default locale and I can import ok.

When I import the file I can convert to decimal number without issue.

You said that you don't have an automatic 'Change Type' step in your query. So at what point are you trying to convert the values to decimal?

When you convert to decimal, it doesn't give an error? Rather it formats your numbers as 123.456.00?

In your XLS file do you see the number as 123.456,00 ?

If you manually try to convert the numbers to decimal using locale do you get this issue?

Phil