Forum Discussion
Data transformation (column with €, M, K)
Hello everyone,
I am new in Power BI and I would like to request your help.
I have a dataset with football players and their current value (cost).
My problem is that the VALUE column has data in the following format (just few examples):
€9,5M
€600K
€10K
The dataset looks something like this (with far more columns):
| Name | Club | Value |
| Neymar Jr | Paris Saint-Germain | €118.5M |
| D. Bernhardt | Vfr Aalen | €600K |
| R. Zapata | Independiente Santa Fe | €70K |
| P. Glykos | PAOK | €1.4M |
The type of data is string and currently I can't do any report whit it.
So I am looking into solution which would:
1) Remove € and M and transform the remaining value into float if it's millions (€9,5M)
2) Remove € and K, transform the remaining value into float and express it in millions (if it's in thousands, K). So if it's 600K it should become 0,6. Or if it's 10K then 0,010.
Thank you in advandce for your help!
7 Replies
Tahreem24Super User
Do the follow step to achieve your goal.
To remove Currency Symbol:
Go to Query Editor --> Select your Column --> Right click on that column --> Select 'Split column by Position' --> Enter 1 in position --> hit ok.
Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!
Hi Neetsmlam !!
Try to create the following column:
Columna = IF( RIGHT(Tabla[Value];1)="K"; MID(Tabla[Value];2;LEN(Tabla[Value])-2) * 1000; IF(RIGHT(Tabla[Value];1)="M"; MID(Tabla[Value];2;LEN(Tabla[Value])-2) * 1000000; MID(Tabla[Value];2;LEN(Tabla[Value])-2) * 1))And then, change the column format to monetary in €.
The result:
- Jimmy801
Community Champion
Hello Neetsmlam ,
use a Tansform.Columns to achieve this. Here an example
let Source = #table ( {"Name","Club","Value"}, { {"Neymar Jr","Paris Saint-Germain","€118.5M"}, {"D. Bernhardt","Vfr Aalen","€600K"}, {"R. Zapata","Independiente Santa Fe","€70K"}, {"P. Glykos","PAOK","€1.4M"} } ), Tranform = Table.TransformColumns ( Source, { { "Value", (transform)=> let DeleteEuro = Text.Replace(transform, "€", ""), Transform = if Text.End(DeleteEuro,1)="M" then Number.From(Text.Replace(DeleteEuro, "M",""),"en-US")*1000000 else if Text.End(DeleteEuro,1)="K" then Number.From(Text.Replace(DeleteEuro, "K",""),"en-US")*1000 else null in Transform , type number } } ) in TranformCopy paste this code to the advanced editor to see how the solution works. Copy a part of this to your query to apply it. Or I could create a function out of it, if you want
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
