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 ...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    You need to use the function

    MID(Txt, StartPosition, NumberOfCharacters)

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