Forum Discussion

I_Like_Pi's avatar
I_Like_Pi
Icon for Resolver II rankResolver II
4 years ago
Solved

Data Cleanse: remove text values from a field if they contain a symbol.

I am trying to clean up some data in an extract that was provided to me and will continue to be provided in the current form. One of the columns is a list of text, I would like to get the desired re...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVgDhrOwchdSUIhDWS68sUYrViVZKTkpUKE8tAuOU5ES9isoqhdy8fKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [input = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Result", each List.Distinct([l=Text.Split([input]," "), r=List.Transform(l,(x)=>Text.Split(x,"."){0})][r])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Result", each Text.Combine(List.Transform(_, Text.From), " "), type text})
in
    #"Extracted Values"

     

I_Like_Pi's avatar
I_Like_Pi
Icon for Resolver II rankResolver II
4 years ago

WOW!! Just realized how dumb that last post was. I thought that whole Let _t was the secret sauce required before the added column. I just realized the added column was all the sauce needed. 

List.Distinct([l=Text.Split([input]," "), r=List.Transform(l,(x)=>Text.Split(x,"."){0})][r])),

I get the next line combined the list but for me this is where the magic happened.

 

Thanks so much!!!