how can I split the column by delimiter in direct query?

[Anonymous]

I have a data table that I reach by direct query mod in Oracle. I have a column with the following format 1111.2222.33333.44444444.5555.666.77777

I would like to split column to 7 seven different column by delimiter so first column has a value of 1111 second is 2222 third 33333 etc. I tried to use in the query editor the split column. However in direct query this feature is not supported.

Is there any way to split that column in the query? If not how can I create a measure that split by a delimiter? For the first I can use left, but after?

Thanks

[Anonymous]

@Anonymous - You could do DAX Calculated Columns, like the following:

Part 1 = 
var _Begin = 1
var _End1 = FIND(".",ParseTest[LongString],_Begin,1000)
return MID(ParseTest[LongString],_Begin, _End1 - _Begin)

Part 7 = 
var _Begin = 1
var _End1 = FIND(".",ParseTest[LongString],_Begin,1000)
var _End2 = FIND(".",ParseTest[LongString],_End1 + 1,1000)
var _End3 = FIND(".",ParseTest[LongString],_End2 + 1,1000)
var _End4 = FIND(".",ParseTest[LongString],_End3 + 1,1000)
var _End5 = FIND(".",ParseTest[LongString],_End4 + 1,1000)
var _End6 = FIND(".",ParseTest[LongString],_End5 + 1,1000)
var _End7 = FIND(".",ParseTest[LongString],_End6 + 1,1000)
return IF(
    _End6 = 1000,
    BLANK(),
    MID(ParseTest[LongString],_End6 + 1, _End7 - _End6 - 1)
)

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

thanks,

part1 working well, when I put it in a new table I see the first 4 digit, exactly what I want,
however in part 7(?) I have error: the end of the input was reached? I dont know why, the dax formula looks like only work wor End4 line after that its grey (looks like inactive to me?)

[Anonymous]

@Anonymous  - could you share a screenshot?

[Waqas_BIspecs]

Handling direct query is sometime challenging but advance Query really helps. Splitting columns or extracting required string can so easily done using SQL query. By using select statement you can pick the columns required and for the column from which substing needs to be extracted can be done using SQL Substring() and charindex() if value needs to be extracted using a delimter in this example i will use delimeter "-", and will extract the string after the delimeter.
select
SUBSTRING(Col_name, charindex('-', Col_name)+1 )+1, LEN(Col_name))

,col2

,col3

,col4

from

(Database Table Name)

substring can also be embeded within Trim function will be remove the spaces from the string 

Trim(SUBSTRING(Col_name, charindex('-', Col_name)+1 )+1, LEN(Col_name)))

This query needs to be written in SQL Statement

-- If this solution works for you please Kuddo

[Anonymous]

yes, I try to copy the link https://imgshare.io/image/powerbi-p4.YZUTH

[Anonymous]

@Anonymous - Please fix the following and let us know whether it works:

ACCOUNT_DETAILS has several places where there is an extra closing bracket: [ACCOUNT_DETAILS]]

There is an extra closing parenthese at the end.

[Anonymous]

yes, sorry, it was my fault, after deleted the ] I have the results. and the results is the value that located in the 'last' 7th place. thats good.

but how can I create column for the remaining 5 values?

in the start I have this value in a colum: and I would like to transfer this values to 7 different column

1111.2222.33333.44444444.5555.666.77777

1111.2222.33333.44444444.5555.666.77777

[#9]

1111.2222.33333.44444444.5555.666.77777

[Anonymous]

@Anonymous - Part 7 is there to demonstrate the pattern. You can do all of the other parts by following the pattern. For example:

Part 4 =
var _Begin = 1
var _End1 = FIND(".",ParseTest[LongString],_Begin,1000)
var _End2 = FIND(".",ParseTest[LongString],_End1 + 1,1000)
var _End3 = FIND(".",ParseTest[LongString],_End2 + 1,1000)
var _End4 = FIND(".",ParseTest[LongString],_End3 + 1,1000)
var _End5 = FIND(".",ParseTest[LongString],_End4 + 1,1000)
var _End6 = FIND(".",ParseTest[LongString],_End5 + 1,1000)
var _End7 = FIND(".",ParseTest[LongString],_End6 + 1,1000)
return IF(
_End6 _End3 = 1000,
BLANK(),
MID(ParseTest[LongString],_End6 _End3+ 1, _End7 _End4 - _End6 _End3 - 1)
)

I guess it could be made more streamlined like this:

Part 4 =
var _Begin = 1
var _End1 = FIND(".",ParseTest[LongString],_Begin,1000)
var _End2 = FIND(".",ParseTest[LongString],_End1 + 1,1000)
var _End_Penultimate = FIND(".",ParseTest[LongString],_End2 + 1,1000) --3rd is Penultimate (2nd to last)
var _End_Final= FIND(".",ParseTest[LongString],_End_Penultimate + 1,1000) --4th is Last
return IF(
_End_Penultimate = 1000,
BLANK(),
MID(ParseTest[LongString],_End_Penultimate + 1, _End_Final  - _End_Penultimate  - 1)
)  --Return can be the same for all splitting columns.

[Anonymous]

Thank you,

I tried to create the part 2

Part 2 =

var _Begin = 1

var _End1 = FIND(".",parsetest[longstring],_Begin,1000)

var _End2 = FIND(".",parsetest[longstring],_End1 + 1,1000)

return IF(

    _End1 = 1000,

    BLANK(),

    MID(parsetest[longstring],_End1 + 1, _End1 - _End2 - 1)

)

I have the following error : single value for column longstring in table parsetest cannot be determinated this can happen when a measure formula, refers to a column that contains many values w/o specifying

Not sure when I made the mistake, but can you advise?

[Anonymous]

@Anonymous - Make sure you are creating Calculated Columns, not Measures. If it still doesn't work, let me know.

[Anonymous]

Sorry Im just a beginner user of PowerBI

Should I go  to edit queries>add column>custom column insert the 2 formula in 2 different column? Even if I try the first 1 I have error:  token eof expected??

And if I use the new column in the calculation ribbon (before the publish icon) I have the following error: syntax for 1 is incorrect.

So can you advise where should I exactly put this ‘formulas’? also I did not see its refer any column, is it normal? In my table I have33 column and I only want to split 1 column lets name it abc123

[Anonymous]

@Anonymous  - These need to be DAX Calculated Columns. You do not need to go to Edit Queries for these.

In my calculation, the table name is "ParseTest" and the column name is "LongString".