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? Ke...
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

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