Split Columns using delimiter in direct query mode

[reetu_k]

I have a direct query and have the need to split my column using a delimiter like '_'. Since I am in the direct query mode I am not able to use the split column option. I want a new column with data from the left of the delimiter.

Here is what my data looks like the following:

Answer1_Right1

Answer2_Right2

123_Right3

[Greg_Deckler]

Pretty sure direct query does not allow that. You would have to switch to import/composite mode.

[Mariusz]

Hi @reetu_k 

Try this, but not sure if it will work in Direct Query Mode

Measure = 
VAR __text = SELECTEDVALUE( 'Table'[Column1] )
RETURN 
    LEFT( 
        __text, 
        SEARCH( "_", __text, 1, 1 ) -1 
    )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[reetu_k]

Hi 

Unfortunately this did not work for me as Search returns the position of the '_' from the string - So I am getting a numerical response back

[dax]

Hi @reetu_k , 

As I know, find and search can't used in calculated column when you using direct query, so I think you could try to use t-sql query to create the split column when you use direct query.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.