Forum Discussion
How to extract text from a string in PowerBI
Hello,
I have a column where values are like this:
{"User":"[email protected]","PageURL":"https://sites/YM-SDS/SitePages/All-Discussions.aspx?dId=6836","DiscussionId":"dId-6836"}
{"User"":"[email protected]","DiscussionId":"dId-5218","PageURL":"https://sites/MY-MIH/SitePages/All-Discussions.aspx?dId=5218&CT=1642601696950&OR=OWA-NT&CID=05c7d8a0-2ae1-bd94-0c42-91f1aba76eac"}
I want to extract this discussion ID from this string e.g DiscussionId:dId-6836. Could anyone have any suggestions?
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
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
4 Replies
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
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 nullHello 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"
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!
