Forum Discussion

cpereyra's avatar
cpereyra
Helper I
6 years ago
Solved

Delimiter with Direct Query

I need to delimit the following column each time "~" shows. It won't let me do in in Edit Query because I have a Direct Query Connection. Does anyone know a dax to grab the text between "~" shows? Keep in mind that I will need to delimit each time the symbol shows.

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi cpereyra,

    Here I updated my sample PBIX file , please check if the newly added measures can get the expected results you want.In addition, if it is possible to handle with it in data source , then load the split columns into Power BI Desktop?

     

    Best Regards

    Rena

6 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    cpereyra 

     

    Try this...

     

     

    Column = 
VAR A = IFERROR(FIND("~",'Table (4)'[Column1]),0)
VAR B = IFERROR(FIND("~",'Table (4)'[Column1],A+1),0)-1
RETURN IFERROR(MID('Table (4)'[Column1],A+1,B-A),'Table (4)'[Column1])

     

     

    If it helps, mark it as a solution

    Kudos are nice too

     

    • cpereyra's avatar
      cpereyra
      Helper I
      6 years ago

      It does work but how would you go about additional delimiters on the same column. 

       

      Ex.

       

      PEC~Insurances - ~Homeowner 

       

      Would need back three columns

      1- PEC

      2 - Insurances

      3 - Homeowner

       

      Anonymous VasTg amitchandak 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi cpereyra,

        Here I updated my sample PBIX file , please check if the newly added measures can get the expected results you want.In addition, if it is possible to handle with it in data source , then load the split columns into Power BI Desktop?

         

        Best Regards

        Rena

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    In Direct Query , You can not create a column, So you need to have a measure. And measure needs calculation. While mid, right , left and search will allow you to do that. You have to move most of your calculations inside x function like sumx, countx, minx etc

     

    Like countx(Table,mid(table[Text],1,4))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Connect on Linkedin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi cpereyra ,

    You can create one measure as below,  please find full details in my sample PBIX file.The character "~" be displayed multiple times in text column "Extra_Fiels.PCC_ALL", here only get the characters between the first "~" and the last "~".

     FPCC = IFERROR (

       MID (

           MAX ( 'PEC Work'[Extra_Fields.PCC_ALL] ),

           FIND ( "~", MAX ( 'PEC Work'[Extra_Fields.PCC_ALL] ) ) + 1,

           FIND (

               "@",

               SUBSTITUTE (

                   MAX ( 'PEC Work'[Extra_Fields.PCC_ALL] ),

                   "~",

                   "@",

                   LEN ( MAX ( 'PEC Work'[Extra_Fields.PCC_ALL] ) )

                       - LEN ( SUBSTITUTE ( MAX ( 'PEC Work'[Extra_Fields.PCC_ALL] ), "~", "" ) )

               ),

               1

           )

               - FIND ( "~", MAX ( 'PEC Work'[Extra_Fields.PCC_ALL] ) ) - 1

       ),

       ""

    )

    Best Regards

    Rena