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

Firstly thanks,

I am trying but i'm struggling to implement that. Your example initiates the let _t from from within

Table.FromRows( "rows from your json import", "column to add in your let _t form").

Do I need to itemize down my table to rows to come back to this form? My table is generated from an import of multiple XLM files so it starts from a folder then translates the binary to an xml table

....  each Xml.Tables(File.Contents([Folder Path]&[Name]))

To which I expand and drop a lot of data.

I dont really have a row by row import.

 

I will keep trying to smash the hammer regardless, hopefully I can find the other end of this potential rabbit hole.