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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sonaliverma
Advocate I
Advocate 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

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

Glad to have found your post! This worked for me:

= Table.AddColumn ( #"Added Conditional Column" , "ColumnName", each Text.Middle ( [Column1] ,
Text.PositionOf ( [Column1] , "ABC" ) , 10 ) )

*[Column1] sample value:
/s/job/ABC1234567/title-in-text?language=ja

Thank you!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors