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

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

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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 313 members 3,637 guests
Please welcome our newest community members: