How to make my BRAND_C field value label from "34 BECKHOFF" To "BECKHOFF" ?

[Anonymous]

[edhans · Accepted solution]

Here you go @Anonymous 

1. Go to the Transform menu, press Extract, Text After delimiter
2. Add a single space
3. Once done, right-click on the column and select Transform, then Trim. This will clean up any double spaces.
4. Close and load.

I removed your calculated column. With this, you have 1 correct column in the model, not an incorrect column and a calculated column.

This is what is in the model now.

And here is your PBIX file back with the working code.

[ryan_mayu]

@Anonymous 

you can still use the similar solutiont that @edhans  provided.

try to put a space in the Delimiter field.

[Anonymous]

Hi Ryan

I start to see the benefit of using power quart for recode , which is very powerful and easy

[edhans]

Yes @Anonymous - try to do this in Power Query. That is what it is designed for. You cannot properly model this in DAX at all. You can get a DAX command to give you the correct column you want, but:

1. It will add a new column and not fix the current column
2. Calculated Columns don't perform as well as "native" columns, and columns brought in via Power Query are considered native. The DAX model doesn't know if the source data was that way or if Power Query massaged it.

THat said, this will work with your data, but I would NOT recommend this path given you can do this in Power Query with the steps I've shown.

Column = 
VAR varSearchPosition = SEARCH(" ", 'TABLE'[BRAND_C], 1, 0)
RETURN
IF(
    varSearchPosition > 0,
    TRIM(RIGHT('TABLE'[BRAND_C], LEN('TABLE'[BRAND_C]) - varSearchPosition)),
    'TABLE'[BRAND_C]
    )

Using Power BI as if Power Query didn't exist is going to severely limit the product and your reports. That is why Power Query is built into the product. If everything could and should be done with DAX, Power Query wouldn't be included.

[Anonymous]

Hi sir

thank you for sharing .

[edhans]

In Power Query, select that column

Select the Transform tab, then the Extract dropdown, then Text After Delimiter

Type "34 " (34 with a space) in the dialog box. It will keep everything after 34_

Then close and load.

[Anonymous]

Hi Edhans

Thank you for your sharing.

since my raw data , not all the number 34 , it consist of 01 till 99 number. May i know how to does using expression. 

[edhans]

@Anonymous use the Text After Delimiter option, and just use a space. It will keep all data after the first space.

If that still doesn't answer your question, can you post some valid and fairly complete data? This won't work if some have dashes, or commas, etc. We need to see a good set of data to truly help, as the 34 BECKHOFF example wasn't comprehensive enough. 👍

While the Calculated Columns will work above, I would advise modeling your data in Power Query as Calculated Columns take up more memory and can slow the model down. If you do it in Power Query, the data comes in clean. I use that kind of logic within measures, but if I truly need to change the data in a column, use a custom column inside Power Query if possible.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns
Creating a Dynamic Date Table in Power Query

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Hi Sir

Yes i am still faving issue , thank you for wanted to help me.

Below is my sample file.

https://www.dropbox.com/sh/p7rthofiytivl3a/AAATCWVgNLyQ0lm_DZ65CClSa?dl=0

[ryan_mayu · Accepted solution]

@Anonymous 

I know why the previous DAX formula doesnot work. There are empty values in the brand_c column.

So len(brand_c)-3 is a negative value. Right function can't select negative postions from the right side.

If you want to use DAX, you can try this.

Column = 
VAR a=len('TABLE'[BRAND_C])
return if(a=0,blank(),right('TABLE'[BRAND_C],a-3))

[Anonymous]

Hi Sir

I just want to apply the script into my PBI file , i realise that it not work again. 

Hope you can share your PBI file which is working for recode 34 Beckhoff to Beckhoff. Not sure where i make mistake ?

[Anonymous]

Hi Ryan

When i apply your script to my actual PBI file , now it does not work again. The error msg is :-

An argument of function 'RIGHT' has the wrong data type or has an invalid value.

https://www.dropbox.com/s/qsh185085pr4grc/SI_TDS_V009.pbix?dl=0

Above is my PBI file

Paul

[edhans · Accepted solution]

Here you go @Anonymous 

1. Go to the Transform menu, press Extract, Text After delimiter
2. Add a single space
3. Once done, right-click on the column and select Transform, then Trim. This will clean up any double spaces.
4. Close and load.

I removed your calculated column. With this, you have 1 correct column in the model, not an incorrect column and a calculated column.

This is what is in the model now.

And here is your PBIX file back with the working code.

[Anonymous]

Hi edhans

Thank you very much for the video , may i know how to create the video ? can i have the software name ?

Paul

[edhans]

It is called ScreenToGif @Anonymous 

Glad I was able to help you get going. Always try to model (transform/change) your data in Power Query if possible. That is what it is designed for. Use DAX for the analysis of data. That is what the "A" in DAX stands for after all. 😁

[Anonymous]

Hi edhans

Thank you for sharing with me the power of power quary. i iwll try to find out more .

Paul

[Anonymous]

@Anonymous -

Create a calculated column as- 

New Column= RIGHT(Table[Column],LEN(Table[Column])-3)

Appreciate your kudos!! Mark my post as solution if this helps.

[Anonymous]

Hi sir, Thank you for sharing , i get error msg below :-

[Anonymous]

Hi @Anonymous 

Use RIGHT(SELECTEDVALUE([ColumnName]), LEN([ColumnName])-3)

Hope this Helps,

Cheers,

Namish B

[Anonymous]

Hi Sir

Thank you for sharing , i still get error :-

[v-robertq-msft]

Hi, @Anonymous 

You  just need to delete "use" before the dax Statement, copy this measure:

New Column=RIGHT(SELECTEDVALUE([BRAND_C]), LEN([BRAND_C])-3)

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.