Valuta decimal separator changing from comma to point

[Anonymous]

Hi,

I want to change the local decimal separator voor Valuta in our dutch version being changed from comma to a point.

I tried to do it by change the reginonal settings to English (VS) and to make an advanced setting in the value of ###.##

Still it shows as in the picture's

In table view

In the visual

[Jimmy801]

Hello @Anonymous 

i suppose that you are reading from a non structured datastructure and therefor you get text in. Afterwards Power BI tries to apply a automatic type identification. As you have in english setting, but the text format shows a different culture. Therefore you have to apply a TransformColumns and forse Power BI to use a culture that has the #.###,00. (example de-DE). Here an example how it looks like

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1MNAxM1CK1QGyLZDYRkC2OZAdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [mBedragBetaald = _t]),
    Transform = Table.TransformColumns
    (
        Source,
        {
            {
                "mBedragBetaald",
                each Number.From(_,"de-DE"),
                type number
            }
        }
    )
in
    Transform

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 

It seems this makes a comma "," instead of a point "." thats excatly the opposite i'm trying to achieve

What do i have to change in the query to make 

[Jimmy801]

Hello @Anonymous 

my solution forces Power BI to format a number written in a different culture into a number in terms of power bi. So the change you want is not a formatting of number from text, applying a culture, but only that a real number in Power BI is shown with .00 instead of ,00. 

Power BI takes your local settings to show a number. So this hasn't to do anything with Power BI but with you Windows installation. To change this goto control panel -> region --> advanced setting and change the format there. Here a screenshot from my local german installation 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 

That youre right,

Only the thing in our company is that is isn't allowed to change these settings.

That the reason why i'm trying to solve this in PowerBI.

[Jimmy801]

Hello @Anonymous 

you could format it again as text, and format as you need it to, but this for sure not the goal, to have numbers formated as text in your data model.

Question: Why is even needed to have the setting like this in a data table?

Because when you are writing a dax-measure or use the formated data in your visualization, this is for sure not and issue to convert it back to a text again.

Hope it helps

Jimmy

[Anonymous]

@Jimmy801 

The reason is pretty complex 🙂

The report is being exported to a CSV.

This has to be read in an application from another party.

For some strange reason they use ";" as separator.

So what we do with all other reports, after export, is changing the ", " to a ";"

However in this report the want the separator as ";" and the notation on money values as a "."

If we do the standaard trick al "," becomes "; " so then they got an issue with the money value.

You can imagine it drives me crazy 🙂

[Jimmy801]

Hello @Anonymous 

sorry, I can't follow. I mean separating columns with ";" is just fine, and nothing has to be adapted prior importing. Just import the data, specifying ";" as separator. However, if the numbers withing the csv-file have this format 0,000.00 apply culture en-US, when it's like this 0.000,00 apply de-DE. 

And if there is a need to format your final measures with the format 0,000.00 use the dax formula "Format".

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 

If i try to set the fomula 

Kolom = FORMAT(FeitNVTZ[mBedragBetaald];000000000.00)

Then directly after pushing enter it gets back to 

Kolom = FORMAT(FeitNVTZ[mBedragBetaald];000000000,00)

[Jimmy801]

Hello @Anonymous 

you have to apply it like this

Power Query (german local setting):

if i want to have this number in a dax  measure reformated lik 0,000.00 then the measure its like this:

SumNumberReformated = Format(Sum(Number[Number]);"#,##0.00 €")

here the final result:

In your case, you have it the other way round 😁

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

So,

When i try this i got an message of "to much arguments for function 'SUM'

Then i'd made a few adjustments butr very time there is a unwanted result.

[Jimmy801]

Hello @Anonymous 

witch column are you referencing?

Could this column already be formated as text?

Jimmy

[Anonymous]

Hi Jimmy,

Tje collumn im reffering to is called "gedeclareerd"

This collum can't be reformated as tekst in the model setting.

[Jimmy801]

Hello @Anonymous 

what I meant is that I can't see that column on the screenshot. And I'm asking myself if this column maybe is already formated as text.

Because the format-function is for sure the function to use, and it works as I proposed.

Jimmy

[Anonymous · Accepted solution]

@Jimmy801 

It finally worked.

I'd used the following formula:

EnglishFormat = SUBSTITUTE(FORMAT('Table'[Collumn],  "#####0.00") ,",",".")

This makes the dutch format to English now. so the separator is changed from comma to point .

Thanx for you help 🙂

[Jimmy801]

Hello

i would appreciate if you could mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy