Forum Discussion
Keep text between 2 deliminators- kind of!
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?
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""" ), """")), ", ")
6 Replies
- Vijay_A_Verma
Most Valuable Professional
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""" ), """")), ", ") 
rubayatyasminCommunity Champion
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
- 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. 🎉
Thanks all will try these suggestions as see if they work 😁👍
