Change encoding of a specific column

[Anonymous]

Hi,

I have a column with lots of characters like Ä Â Ñ etcetera. Instead of doing a search and replace for each of these characters, I found this piece of code which changes the encoding from Windows 1251 to Ascii. This gets rids of all 'custom' characters:

= Table.AddColumn(source, "Custom Column", each Text.FromBinary(Text.ToBinary([Product Name], 1251), TextEncoding.Ascii))

However, I don't want to have another column, I want the encoding to happen directly in my Product Name column. What I'm doing now is deleting my original Product name column and then renaming the new Custom Column. But that's two extra steps.

Is there a line of code I can replace this code with so that I do not get an additional column, but instead the encoding gets changed immediately in the Product Name column?

Thank you in advance
Bas

[zgraja]

This worked for me:
= Table.TransformColumns(source,{{"Product Name", each Text.FromBinary(Text.ToBinary(_, 1251), TextEncoding.Ascii)}})

[Ultramarp09]

Create a custom column in Power query, in your dataset find the special characters you need to replace, according to your post it must be in spanish so there's only 12 special characters 6 for each case
Use this formula for the custom column
=Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(
Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace([your_column],
"á", "á"), "Ã", "Á"),
"ã‰", "é"), "É", "É"),
"í", "í"), "Í", "Í"),
"ã“", "ó"), "Ó", "Ó"),
"ú", "ú"), "Ú", "Ú"),
"ñ", "ñ"), "Ñ", "Ñ")

Remember, this special characters i show here could not be the same as yours, i'm only sure about the é and ó.

replace the special characters for the ones in your dataset and that should do the trick, ovbiously the easiest way to do this is to encode the dataset as utf8 friendly and then it should recognize all those special characters.

[dstoma]

Was there a solution posted?  I clicked on "Go to Solution", but I didn't see it.  Was it deleted at some point?

[amitchandak]

@Anonymous , is it a database source or CSV? I rememember in case of CSV it ask for encoding at time of loading.

[Anonymous]

Thanks for the fast replies @Greg_Deckler  and @amitchandak .

The data is not a csv, there's many steps before I get into the encoding step, it's a combination of many files and filetypes. 

I always assumed that the more steps there are, the slower the refresh time becomes. Hence I'm cleaning up my queries to try and reduce the amount of steps and combining things as much as possible. But if you're saying it doesn't affect load times then I might just leave it like this.

In general I have a lot of queries where I first have to create a custom column based on an original column. I then remove the original column and rename the custom column to the same name as the original. If there's an easy way to fix this (perhaps there's a piece of code that should be changed in any step to make the change in the same column instead of in a custom column) I'd love to hear about it!

Thanks

Bas

[AllisonKennedy · Accepted solution]

The only advice I would give is to rename the original column BEFORE you add the column. Then you can save the step of renaming the new column because you can use the correct name when you create the custom column.

More steps doesn't necessarily mean slower refresh, depends what you're doing in each step.

If you have lots of steps where you change the data type for example, and change from number to currency then back to number, that should be cleaned up.

Hope that makes sense?

[Anonymous]

Good advice, thanks @AllisonKennedy !

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your data model and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Greg_Deckler]

@Anonymous - Create the new column with the code. Delete the old column. Rename the column. Steps are your friend.