Keep text between 2 deliminators- kind of!

[Jamieneedshelp]

Hi All

When importing termstore data to power bi we get lots of unwanted text. Is there any way to just keep Oranges and Apples from the below and remove the rest?

[{"Label":"Oranges","TermID":3fij4h4jkdkddjdj4ndjdke3"},{"Label":"Apples","TermID":3fij4h4jkdkddjdj4ndjdke3"}]

So something like keep text between [{"Label":  and ,"TermID": so desired result would just be Oranges, Apples

Many Thanks

[Jamieneedshelp]

Thanks all will try these suggestions as see if they work 😁👍

[Anonymous]

I would first replace all of the quotes with "~" or any character not appearing in the text, to avoid any text-quote issues. Then you can use Text.BetweenDelimiters, using ":~" and "~,".

--Nate

[Vijay_A_Verma · Accepted solution]

Use the below formula in a custom column (here Text is your column name which you will need to change)

Text.Combine(List.Transform(Text.Split([Text], "},{"), (x)=> Text.Trim(Text.BetweenDelimiters(x, """Label"":", ",""TermID""" ), """")), ", ")

[Greg_Deckler]

@Jamieneedshelp This is hacky:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiq6OUfJJTErNiVGyilHyL0rMS08tjlHSiVEKSS3K9XQBChunZWaZZJhkZadkp6RkpWSZ5AHJ7FTjGKVaHRTdjgUFOSRojlWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",""TermID"":", QuoteStyle.None), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByDelimiter(",{""Label"":", QuoteStyle.None), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.2.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","[{""Label"":","",Replacer.ReplaceText,{"Column1.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","""","",Replacer.ReplaceText,{"Column1.1", "Column1.2.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"Column1.1", "Column1.2.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

[rubayatyasmin]

So basically, Greg just M coded what I wrote in plain text. 😁 

Problem solved. 🎉

[rubayatyasmin]

Hi @Jamieneedshelp 

Try splitting it into two cols first then you should have terms and labels column. In label column perform power query transformation, split by delimeter. As delimeter select :" this way you will get Oranges, apples in another column. Remove the other redundant columns as you finish. 

If it's hard to understand provide me some demo data I will upload a pbix for you. 

Thanks