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.
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.;
input | desired result |
abc abc jkl edr edr.gyt | abc jkl edr |
cba wer wer dca.xyz mno | cba 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 returns
abc jkl edr edr.gyt
cba 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.
Solved! Go to Solution.
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"
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(_, ".")
)
),
" "
)
)
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!!!
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.
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"
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.