How to extract text from a string in PowerBI

[sonaliverma]

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?

[PhilipTreacy · Accepted solution]

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"

Regards

Phil

[Syndicate_Admin]

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 · Accepted solution]

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

[sonaliverma]

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 · Accepted solution]

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"

Regards

Phil