Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ericet
Helper I
Helper I

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)

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @ericet 

 

Download sample PBIX file

 

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.

 

repdi.png

 

 

Giving this result

 

repdi2.png

 

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

 

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
Ahmedx
Super User
Super User

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}))

https://1drv.ms/v/s!AiUZ0Ws7G26RiHgbeQ-8TNbRN1BY?e=uSWuZn

Great answer with the video !, thank you. 

PhilipTreacy
Super User
Super User

Hi @ericet 

 

Download sample PBIX file

 

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.

 

repdi.png

 

 

Giving this result

 

repdi2.png

 

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

 

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.