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 result i.e.;inputdesired resultabc abc jkl edr edr.gytabc jkl edrcba wer wer dca.xyz mnocba wer dca mno Essentially I need to remove the characters after the period (if they exist) and then dedup the list.Or at least that is how my head is approching it.I started with;Text.Combine(List.Distinct(Text.Split([input], " ")), " ")which returnsabc jkl edr edr.gytcba wer dca.xyz mno So I have nothing for stripping the .gyt or .xyz prior to the distinct.I am happy to do this in multiple passes/columns.For the example above I used 3 character blocks and a period but in reality they are of variable length and there are a couple other symbols.Does anyone know how some ideas to strip the text after the dot?Thanks in advance for any takers.

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

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"

4 Replies

Vijay_A_Verma

Most Valuable Professional

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
Resolver 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.
I_Like_Pi
Resolver 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!!!

AlexisOlson

Super User

4 years ago

You can also use Text.BeforeDelimiter.

Table.AddColumn(
    #"prev step",
    "desired result",
    each Text.Combine(
             List.Distinct(
                 List.Transform(
                     Text.Split([input], " "),
                     each Text.BeforeDelimiter(_, ".")
                 )
             ),
             " "
         )
)

Forum Discussion

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

4 Replies

Related content

Data Cleansing Questions

Trademark symbol

Missing sigma symbol

data cleansing

Header Symbols?

input	desired result
abc abc jkl edr edr.gyt	abc jkl edr
cba wer wer dca.xyz mno	cba wer dca mno