Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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""" ), """")), ", ")
Thanks all will try these suggestions as see if they work 😁👍
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
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""" ), """")), ", ")
@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"
So basically, Greg just M coded what I wrote in plain text. 😁
Problem solved. 🎉
Proud to be a Super User!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.