cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sonaliverma
Helper I
Helper I

How to extract text from a string in PowerBI

Hello, 

 

I have a column where values are like this:

 

{"User":"Reese.Allphin@yes.com","PageURL":"https://sites/YM-SDS/SitePages/All-Discussions.aspx?dId=6836","DiscussionId":"dId-6836"}

 

{"User"":"Craig.Marshall2@gio.com","DiscussionId":"dId-5218","PageURL":"https://sites/MY-MIH/SitePages/All-Discussions.aspx?dId=5218&CT=1642601696950&OR=OWA-NT&CID=05c7d8a0..."}

 

 

I want to extract this discussion ID from this string e.g DiscussionId:dId-6836. Could anyone have any suggestions?

 

 

2 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
Super User

Hi @sonaliverma 

 

Download example PBIX file with solution

 

Add a Custom Column with this code

 

Table.AddColumn(Source, "Custom", each Text.Replace(Text.Middle([Column1], Text.PositionOf([Column1] , "DiscussionId"), 23), """:""", ":"))

 

 

Here's the full example code in my example PBIX file (above)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9di4JAFIb/y1w3H1rOmiAVebHCWqFJRNPFoIMOuCkeg5bov2fT0u5FUJfnvO95eM5uh84CpaBagTyBYqVAkVlVNaU+TH8UkKz+Fmgg0EoWKo2/TKvsugY8SkF3Cug2wkmQ0KQfbiWg/TkONGRHAF0fgEhoTpM8zH3uDrmB/aVhboh9iu/pBe0H/5xMOm+lLkgkWyhlVdnTQtcPr6cox7bcF9bRFkfh5zvWBnZkzObztW/xkc2Zxcd87LD7dhn7y80ML9a/pTDwmZN95K5khBDz0f4K", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Replace(Text.Middle([Column1], Text.PositionOf([Column1] , "DiscussionId"), 23), """:""", ":"))
in
    #"Added Custom"

 

 

ext.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Vijay_A_Verma
Super User
Super User

Use below formula in a custom column (replace Data with your column name) if you need to extract only dId-6836. It doesn't make sense to extract full string DiscussionId:dId-6836 but only dId-6836

= try Text.Split(Text.Split([Data],"""DiscussionId"":"""){1},""""){0} otherwise null

 

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

Use below formula in a custom column (replace Data with your column name) if you need to extract only dId-6836. It doesn't make sense to extract full string DiscussionId:dId-6836 but only dId-6836

= try Text.Split(Text.Split([Data],"""DiscussionId"":"""){1},""""){0} otherwise null

 

Hello Vijay,

 

I am facing a similar issue.

 

I want to extract the discussionID but I have multiple discussionID now in a row. I want all the IDs in one column. Could you please help.

 

Ex: "https://sites/YM-SDS/SitePages/All-Discussions.aspx?dId=6836","DiscussionId":"dId-6836","DiscussionId":"dId-6736","DiscussionId":"dId-6436"

 

 

PhilipTreacy
Super User
Super User

Hi @sonaliverma 

 

Download example PBIX file with solution

 

Add a Custom Column with this code

 

Table.AddColumn(Source, "Custom", each Text.Replace(Text.Middle([Column1], Text.PositionOf([Column1] , "DiscussionId"), 23), """:""", ":"))

 

 

Here's the full example code in my example PBIX file (above)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9di4JAFIb/y1w3H1rOmiAVebHCWqFJRNPFoIMOuCkeg5bov2fT0u5FUJfnvO95eM5uh84CpaBagTyBYqVAkVlVNaU+TH8UkKz+Fmgg0EoWKo2/TKvsugY8SkF3Cug2wkmQ0KQfbiWg/TkONGRHAF0fgEhoTpM8zH3uDrmB/aVhboh9iu/pBe0H/5xMOm+lLkgkWyhlVdnTQtcPr6cox7bcF9bRFkfh5zvWBnZkzObztW/xkc2Zxcd87LD7dhn7y80ML9a/pTDwmZN95K5khBDz0f4K", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Replace(Text.Middle([Column1], Text.PositionOf([Column1] , "DiscussionId"), 23), """:""", ":"))
in
    #"Added Custom"

 

 

ext.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Kudoed Authors