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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Change encoding of a specific column

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

 

1 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?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

7 REPLIES 7
dstoma
Frequent Visitor

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

amitchandak
Super User
Super User

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

Anonymous
Not applicable

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

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?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Good advice, thanks @AllisonKennedy !

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.