Forum Discussion
How do I use a function on my 15,000+ column ?
How do I use a function on my 15,000+ column ?
I have a table with a column of 15,000 plus names, some of these names were
sometimes entered with a french accentted character and sometimes not.
Example: Éric or Eric
Now, I found with Chris Web blog post that solves my problem
Removing Diacritics From Text In Power Query by Chris Web
but I don't know how to use it ?
I have the function but how do I execute it on my 15,000+ name in my column ?
Eric (Montreal, Canada)
Hi ericet
Using Chris's code, create a separate blank query and copy/paste the function code into it, then save it. I named my function ReplaceDiacritic. You now have a custom function you can use.
To actually use the function, add a Custom Column to your table/data and call the function like so, passing int he name column as the function input.
Giving this result
Here's my example query but if you look at the file above it'll make more sense.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOtxZlJmsFKsTreSYe3hlTmYqmB12eGVRfl5mYSmE6waSqQAzgw6vzK0Es3wOr8zPg0gXJeYdXp6fWYzKg+g93JmTWZyYlFqSAea6A01OzEnJzAPKxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each ReplaceDiacritic([Name])) in #"Added Custom"regards
Phil
4 Replies
to understand that it is easy to do, watch my video
= Table.TransformColumns(#"Changed Type", List.Transform(Table.ColumnNames(#"Changed Type"),(i)=> {i,(x)=> RemoveExcessSpaces(x), type text}))Great answer with the video !, thank you.
Hi ericet
Using Chris's code, create a separate blank query and copy/paste the function code into it, then save it. I named my function ReplaceDiacritic. You now have a custom function you can use.
To actually use the function, add a Custom Column to your table/data and call the function like so, passing int he name column as the function input.
Giving this result
Here's my example query but if you look at the file above it'll make more sense.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOtxZlJmsFKsTreSYe3hlTmYqmB12eGVRfl5mYSmE6waSqQAzgw6vzK0Es3wOr8zPg0gXJeYdXp6fWYzKg+g93JmTWZyYlFqSAea6A01OzEnJzAPKxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each ReplaceDiacritic([Name])) in #"Added Custom"regards
Phil
This is awesome, the PBIX file and your steps to make it work on my file made this solution the right solution for me. I was able to make it work on my file.
Your efforts are appreciated, thank you so much. Have a great day.
