Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
My database has some numerical fields that come in text and some of them have commas, by the time to convert the text to a value, the comma disappear and my number become "bigger". For example if I have 100,00 I will have 10000, I also attach a printscreen .
I hope that anyone can help me
Best Regards
Solved! Go to Solution.
Oh, I guess I misunderstood. Then replace "," with "." instead of blank.
I hope you are looking to achieve this...
I've splitted the Data field into two parts i.e before comma and after comma. Duplicated Part2 field to be a decimal type.
Then added a final output field as below
FinalOutput = IF(VALUE(NumText[Part2Num])=0,NumText[Part1],NumText[Data])
Proud to be a PBI Community Champion
Hmm, I'd do this in Query Editor. Right click on the column and replace "," with blank. Then change data type to number.
So, are you saying that the commas in your case are decimals? So that 100,00 should be 100.00?
100,00 should be 100 and 99,85 should be 99,85 and not 9985 for example. The commas disappear at all
These are always tough because I really can't play with the regional language settings which I believe handle things like decimals and dates in different formats. Sounds like your's might be set to English perhaps? Seems like if you had the right regional settings that Power BI would recognize those commas as decimals and convert them correctly to numbers by just changine the type.
Oh, I guess I misunderstood. Then replace "," with "." instead of blank.
This one comment saved my life today. Thanks man!
I've used several formulas to reach my final goal, and I was successfull, thank you guys
Test = VALUE(if(find(",";Production[PRODUCTION EBITDA YTD];1;0)>0;replace(Production[PRODUCTION EBITDA YTD];find(",";Production[PRODUCTION EBITDA YTD];1;0);1;".");Production[PRODUCTION EBITDA YTD]))
Best regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |