Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

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

12 REPLIES 12
Anonymous
Not applicable

@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
Not applicable

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
Not applicable

@Anonymous  - could you share a screenshot?

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

 

Waqas_BIspecs_1-1691519775113.png

 

-- If this solution works for you please Kuddo

 

 

Anonymous
Not applicable

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

 

Anonymous
Not applicable

@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
Not applicable

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

 

Anonymous
Not applicable

@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
Not applicable

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
Not applicable

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

Anonymous
Not applicable

 

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
Not applicable

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

Create Calculated Column - 1.PNG

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.