Dealing with euro formatting of decimal numbers

[nh27]

Hi All,

I'm consolidating around 40 excel files in PQ through a helper query. Each file contains a table with the same name, however I have two columns that are decimal numbers but the files consist of data input against UK and EU regional settings therefore for example the value of 0.5 may appear as ,5 or 0,5 in other files. 

When I pull this into PQ no matter what I've tried to do, after changing the column to type Decimal it throws an error on the euro formatted values. I've tried to do a replace on , to . but that can only be done whilst the column type is text however when I convert it back to decimal it gives an error again.

Is there any way to overcome this?

[v-tsaipranay]

Hi @nh27 ,

Could you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

[v-tsaipranay]

Hello @nh27 ,

I wanted to check if you had the opportunity to review the information provided by me and @PwerQueryKees . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hi @nh27 ,

Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to personally thank @PwerQueryKees  and @ronrsnfld , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I would appreciate it if you could confirm whether the solution provided by PwerQueryKees has resolved your issue.

Additionally, here are some detailed troubleshooting steps for your better understanding:

• When loading the data into PQ, ensure that the columns with decimal numbers are imported as text by changing the data type to Text in the PQ editor.
• In the PQ editor, select the column with the decimal numbers, go to the Transform tab, and click on Replace Values.
• In the Replace Values dialog box, enter a comma, in the value to find field and a period. in the Replace With field, then click OK to apply the changes.
• After replacing the commas, select the column again, go to the Transform tab, and change the data type to Decimal Number. PQ should now be able to convert the values without any errors.

For more detailed information, you can refer to the following links:

Set a locale or region for data (Power Query) - Microsoft Support

Power Query - How to import a CSV file that does not match your locale - Microsoft Community

I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[v-tsaipranay]

Hi @nh27 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hi @nh27 ,

I wanted to follow up on our previous suggestions regarding Dealing with euro formatting of decimal numbers. We would love to hear back from you to ensure we can assist you further.

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thankyou.

[v-tsaipranay]

Hi @nh27 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[nh27]

Hi,

Unfortunately I've still yet to manage to resolve this despite all the kind potential solutions provided. I can't seem to get the locale formatting to enforce.

Furthermore, if I did manage to get this to work I will have a similar issue again as the data includes UK and EU formatting so there will be entries with a . and those with a , so any option I explore would involve trying to do a find/replace.

[PwerQueryKees]

@nh27 , you mentioned you could solve this in the source.

I think this is what you need because the thousends separator should NOT be visible in PowerQuery because it is part of the number formatting. 

First

Check to see if I am right and your numbers are stored as strings in Excel.

Normally, if you just type the number 00004551.9, Excell wil do this:

• It will store the numerical value: prefixed zeroes are ignored.
• It assignes General format
• Show the actual number in both formula bar and the cell
• Right aligned (if the cell alignment was left to its default)

If the number was added as text, it will look like this:

• Thousends separator is not only in the cell but also in the formula bar
• If you select the cell, the little yellow warning sign is shown.
  If you click the sign you get the "Number Stored as Text" message.
• It is Left aligned (if the cell alignment was left to its default)

Depending on the actual cell content, there are multiple ways to fix:

• Press F2 and enter. If the correct seprators for the locale of your excel was used, the cell content will be interpreted as a number.
• Change/Replace
  • All spaces with nothing
  • thounds separator with nothing
  • decimal separator with itself
  • any currency signs with nothing

I hope this helps...

If you insist on solving this in powerquery you have to give us something more to work with. There may be patterns in the data that can be utilized, but we have to see those for ourselves.

So please provide (a OneDrive link) to some representative sample sheets...

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[v-tsaipranay]

Hi @nh27 ,

Thank you for providing clarification. If the locale settings aren't working for you, and your dataset contains both UK (using '.' for decimal) and EU (using ',' for decimal) formats in the same column, we need a reliable method to standardize these values before converting them to decimal numbers.

Try this as an approach:

1. Ensure the column is treated as text initially.
2. Use Power Query (M code) to intelligently detect and transform numbers.
3. Convert the standardized values to Decimal format.

If you are still unable to resolve the issue after trying this, I suggest raising a support ticket. Please follow the link below to raise the support ticket.

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[ronrsnfld]

In Excel, ensure the data is all TEXT (all entries should be left justified).

Then, in PQ, when you set the data type, be sure to specify the culture. 

In the UI, you would select Locale from the right-click drop down menu:

And then select the appropriate data type and culture from the dialog:

[PwerQueryKees · Accepted solution]

You are not supplying any or screenshots of errors or anything, so I am just guessing: 

I suspect the excel files contain numbers fomatted as text. In other words, Excel does not interpret them as numbers.

Normally, when when numbers are interpreted as numbers, PQ does not care about the formatting, it imports the underlying number.

If error checking is on in the spreadsheet, it even gives warning on such cells.

If you can't fix the source, you have to dig in and see what text values you need to convert to numbers in PQ. That may be hard. Spaces, inconsisten number of decimal places, other funy characters will all have to be dealt with.

If you need more help, we need more details 🙂

Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[nh27]

Here is the data pulling through from the table in excel which is formatted as a number:

As soon as I change this to a decimal column:

As you can see some of the values that were 4,6 that should be 4.6 are now showing as 46.

Error cells:

[PwerQueryKees]

Your first shot shows that PW pulls the values in as text values. In excel itself these are then text values I suspect. Check the data you show also in Excel. Are they true numbers? Are they automatically right-aligned? The right align button should NOT be bold.  I am correct in understanding it is excel and not .csv, right?

If you can't fix teh source, do you know which sheets use european formatting? If so, you could do a Number.From to fix them.

Let's first see where the issue is....