Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Valuta decimal separator changing from comma to point

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 viewIn table view

In the visualIn the visual

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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 🙂

 

View solution in original post

15 REPLIES 15
Jimmy801
Community Champion
Community Champion

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
Not applicable

@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 

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 

image.png


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
Not applicable

@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.

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
Not applicable

@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 🙂

 

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
Not applicable

@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)
 
 

Hello @Anonymous 

 

you have to apply it like this

Power Query (german local setting):

image.png

 

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:

image.png

 

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
Not applicable

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.

 

 

 

pogingen.PNG

Hello @Anonymous 

 

witch column are you referencing?

Could this column already be formated as text?

 

Jimmy

Anonymous
Not applicable

Hi Jimmy,

Tje collumn im reffering to is called "gedeclareerd"

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

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
Not applicable

@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 🙂

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.