Forum Discussion

reetu_k's avatar
reetu_k
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Split Columns using delimiter in direct query mode

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

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      reetu_k
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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's avatar
        dax
        Icon for Community Support rankCommunity Support
        6 years ago

        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.