extracting values from mid strings - varying character length

[Anonymous]

Hello, 

I have a column of various values in the following format: 

XX_123rgdt_XXXXX

XX_343dsbceurfvr_XXXX

XXXX_323d_XXXXX

and would like to extract into separate column values between _ and _ from above. I thought of using MID() formula but these values have different length in the middle so it would not work for all cases. does anyone know how to do this?

[Anonymous · Accepted solution]

HI @Anonymous,

If the fact table records are similar to the sample data that you shared, you can use the following formula(path function) to create a new calculated colunmn to extract the specific part of strings:

New Column= PATHITEM ( SUBSTITUTE ( [Column], "_", "|" ), 2 )

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous split column by _ and then use the middle column

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/