Data transformation (column with €, M, K)

[Neetsmlam]

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!

[jherralo95]

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:

[Mariusz · Accepted solution]

Hi @Neetsmlam 

Please see the attached File with a solution included.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Neetsmlam]

Thank you very much @Mariusz !
It works!
Could you please explain your code (if it doesn't bother you)

[Tahreem24]

@Neetsmlam ,

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!

[Mariusz]

Hi @Neetsmlam 

Can you create a data sample representing your request

Thanks

Mariusz

[Neetsmlam]

Hi @Mariusz ,

Thank you for you response.
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

[Jimmy801]

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
    Tranform

Copy 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