Forum Discussion

Diptarup's avatar
Diptarup
Helper II
5 years ago
Solved

Separating Text from Delimiters

I have a created table (created through DAX) and one of the columns have the Alpha numeric values which I need to separate.

As this table is created through DAX, not sure if power query can be used and hence looking for Column wise formula.Table below

 

Activity

A.1.2.1. Copper

A.1.3.1 Aluminium

A.1.12.2. Bronze

A.1.13.1 Iron

A 3 3 Silver

Gold

The result expected is given below:-

Activity

Copper

Aluminium

Bronze

Iron

Silver

Gold

 

Any help will be highly appreciated. 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Diptarup 

    I'm afraid there is no way to meet your needs .The language we use in Query Editor is M language ,but in the Desktop view , we use DAX .Query Editor cannot identify the column or table that created by DAX .

    If the column you provide is a regular column, I think I have a way to achieve the result you want .

    Switch to Power Query page , then follow the steps below.

    (1)Replace the “A” with “” in column [Activity] .

    (2)Replace the value “.1.3.1 luminium” with “.1.3.1 Aluminium” ,because when we are doing the first step, replacing the “A” in the entire column, the latter “A” is also replaced, so we need to add “A” to this value separately .

    (3)Add a custom column like this

     

    Activity 1=Text.Select([Activity],{"A".."z"})

     

    The final result is as shown :

    I have attached my pbix file , you can refer to it .

     

    Best Regards

    Community Support Team _ Ailsa Tao

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

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    Diptarup 

    I'm not sure how complicated your real data is.

    here is a workaround. maybe you can try to create a column from examples in PQ.

    2. Enter Aluminium

    Then you can see the wrong result for Silver, then type Silver in the corresponding cell. Then you can get the expected result

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Diptarup 

    I'm afraid there is no way to meet your needs .The language we use in Query Editor is M language ,but in the Desktop view , we use DAX .Query Editor cannot identify the column or table that created by DAX .

    If the column you provide is a regular column, I think I have a way to achieve the result you want .

    Switch to Power Query page , then follow the steps below.

    (1)Replace the “A” with “” in column [Activity] .

    (2)Replace the value “.1.3.1 luminium” with “.1.3.1 Aluminium” ,because when we are doing the first step, replacing the “A” in the entire column, the latter “A” is also replaced, so we need to add “A” to this value separately .

    (3)Add a custom column like this

     

    Activity 1=Text.Select([Activity],{"A".."z"})

     

    The final result is as shown :

    I have attached my pbix file , you can refer to it .

     

    Best Regards

    Community Support Team _ Ailsa Tao

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