Delimiter with Direct Query

[cpereyra]

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.

[v-yiruan-msft]

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

[amitchandak]

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

[VasTg]

@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]

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

@v-yiruan-msft @VasTg @amitchandak 

[v-yiruan-msft · Accepted solution]

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

[Adeel_Yousaf]

Your link is removed.