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
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
Solved! Go to Solution.
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 @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
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
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
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
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
If i try to set the fomula
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
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.
Hello @Anonymous
witch column are you referencing?
Could this column already be formated as text?
Jimmy
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
56 | |
27 | |
17 | |
13 |