Forum Discussion

nkasdali's avatar
nkasdali
Microsoft Employee
8 years ago
Solved

Substring in Power BI ?!

Hi All,

 

I'm coming from Qlikview world, so i'm looking the equivalent of SUBSTRING function.

 

So, this function will return a string in a specific position, exemple

 

Value : TD_AA_BB

 

i wan to have what after the second "_". Then in Qlikview the function will be  Substring("TD_AA_BB","_",2)

 

Do you how can i do to get the same in Power Bi ?

 

 

Thanks

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    You need to use the function

    MID(Txt, StartPosition, NumberOfCharacters)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    You need to use the function

    MID(Txt, StartPosition, NumberOfCharacters)

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      8 years ago

      "In Power BI" can mean either DAX (see previous post) or Power Query.

       

      A Power Query solution would be to use the option Text After Delimiter under "Extract" in the "Transform" or "Add Column" menu.

       

      Screenshot, including the generated formula that will give you the part after the second "_":

       

    • nkasdali's avatar
      nkasdali
      Microsoft Employee
      8 years ago

      Thanks, it works well.

       

      But, do you know how can i do to not have the (Blank) value ?

       

      This is my expression :

       

      APP_Cible = if(left(v_hist_usage_sid[UserName];6)="US_BDF";mid(v_hist_usage_sid[UserName];8;3))

       

      thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        nkasdali,

        You can choose one of the following options to exclude the blank.


        1. Drag the APP-Cible column to page level filters, then set its value as "Not blank".


        2. Create a new column using DAX below.

        newcolumn = IF(ISBLANK(v_hist_usage_sid[APP_Cible]);"enter your expected value";v_hist_usage_sid[APP_Cible])



        Regards,
        Lydia