Substring in Power BI ?!

[nkasdali]

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

[gbarrachina · Accepted solution]

You need to use the function

MID(Txt, StartPosition, NumberOfCharacters)

[nkasdali]

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

[v-yuezhe-msft]

@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

[MarcelBeug]

"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 "_":