Forum Discussion

PascalT's avatar
PascalT
Helper I
9 years ago
Solved

[DAX] isolate text from text

Dear PBI Experts   I guess that this is a very noby question... but i've a hard time to solve it.   In a column, I've several line structured like that: /products/coral-dental-33600002-17 /prod...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Well, I'm not a DAX expert, but from my Excel background I constructed the following DAX formula.
    Prerequisite is that your strings don't contain any ^ character.

     

    =right([String],len([String])-find("^",substitute([String],"-","^",-1+len([String])-len(SUBSTITUTE([String],"-","")))))
MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

In Power Query it can be done with:

 

= Table.AddColumn(Source, "ProductNumber", each Text.Range([String],1+List.First(List.LastN(Text.PositionOf([String],"-",Occurrence.All),2))))
  • PascalT's avatar
    PascalT
    Helper I
    9 years ago

    Hi Marcel

     

    Thank you very much. As i'm not yet very confortable with the PowerQuery, is it possible to do it in DAX. I'll create a new column for it.

     

    In all the case, thank you very much, your help is really appreaciated.

     

    Pascal

    • Sean's avatar
      Sean
      Community Champion
      9 years ago

      PascalT

      If the ProductNumber is always 11 characters you are in business :smileyhappy:

      Hope this helps! :smileyhappy:

      • PascalT's avatar
        PascalT
        Helper I
        9 years ago

        Hi and thank you too.

        Unfortunatly, i can't do like that as the numbers are not always with the same length.

        Most of the time, there are 8 + 3 numbers but it can happend that we have 7 + 3 or 8 + 2

         

        Thank you for your help

         

        Pascal