topic Re: How to delete everything after a dash and put the remaining text on 4 characters in DAX? in DAX Commands and Tips

How to delete everything after a dash and put the remaining text on 4 characters in DAX?

matdub40 — Thu, 06 May 2021 15:06:37 GMT

I have in Visual Studio a table `Y2_Sales` which contains a column "`color`" with values like this :

85-GREY MARLE
90-BLACK
90-BLACK
1000-WHITE
49-NAVY
90-BLACK
3021-BONE

I would like to make a calculated column in my template and delete everything after the dash and put the remaining numbers on 4 characters, which would give :

0085
0090
0090
1000
0049
0090
3021

I tried this formula but it returns me for example `90-` how to remove this last dash?

=LEFT([color],
     IF (SEARCH("-",[color],1,0) > 0,
         SEARCH("-",[color],1,0),
           LEN([color])
          )
      )

Could someone help me?

Re: How to delete everything after a dash and put the remaining text on 4 characters in DAX?

Jihwan_Kim — Thu, 06 May 2021 09:07:55 GMT

Hi, matdub40

I am not sure if you want to do this in Power Query Editor.

I think it is a bit easier doing this in Power Query Editor like below (link down below)

https://www.dropbox.com/s/oxp7d0nks97tort/matdub.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: How to delete everything after a dash and put the remaining text on 4 characters in DAX?

matdub40 — Thu, 06 May 2021 09:20:20 GMT

Hi Jihwan_Kim ,

Thank you for your answer, I will look into it, but I would prefer to do it in Visual Studio with a calculated column without having to modify the Power BI part.

You have made two distinct columns that separate my two values, I have only one column your two columns separated by a dash like: 90-BLACK