cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
annie0308 Frequent Visitor
Frequent Visitor

In PowerBI Query Editor, split column using starting and ending delimiter

Hi,

 

I have a column of data which is of the format given below:

 

[{'name': 'TriStar Pictures', 'id': 559}, {'name': 'Teitler Film', 'id': 2550}, {'name': 'Interscope Communications', 'id': 10201}]

 

I want to split this column of data into multiple columns, with delimiter start as '{' and delimiter end as '}'.

Endresult expected:

Column 1Column 2Column 3
{'name': 'TriStar Pictures', 'id': 559}{'name': 'Teitler Film', 'id': 2550}{'name': 'Interscope Communications', 'id': 10201}

 

If I use the delimiter as comma, then even then the result would be 6 columns, which is not what I want. Can you please suggest how this can be done in Power BI Query Editor using the Split column feature.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
adityavighne Regular Visitor
Regular Visitor

Re: In PowerBI Query Editor, split column using starting and ending delimiter

You can use below DAX


=PATHITEM(SUBSTITUTE(Person[Full Name], “{”, “|”), 1)

 

1 is for previous text {

2 for after text  {

2 REPLIES 2
Highlighted
adityavighne Regular Visitor
Regular Visitor

Re: In PowerBI Query Editor, split column using starting and ending delimiter

You can use below DAX


=PATHITEM(SUBSTITUTE(Person[Full Name], “{”, “|”), 1)

 

1 is for previous text {

2 for after text  {

annie0308 Frequent Visitor
Frequent Visitor

Re: In PowerBI Query Editor, split column using starting and ending delimiter

Thanks Aditya for your quick response. It really helped in resolving the issue. Thanks once again.