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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
KCinMelbourne
Resolver I
Resolver I

Replace Value in Whole Table

Hi guys,

 

I've had issue in the past where Project Online data pulls in with 'non printable characters'. It's a pain because it appears as a space but when you try to (for example) merge the table with another table, they won't match.  To overcome this, I wanted to perform a find and replace on the table data in Query Editor when it is imported to replace the non-printable character with " " (a space).

 

The issue I am concerned about is what happens if a column is added or removed from the table that is being imported.  This happened only yesterday causing the query to fail because the column couldn't be found.  

 

Hoping someone could tell me: 

  1. Can I do a find and replace without specifying columns
  2. If not, can anyone propose the code that can be used to attempt to perform the replace but just skip it if the column can't be found. 

Thanks
KC

1 ACCEPTED SOLUTION

You can do this by using the Table.ColumnNames function in replacement of the specific column list.  E.G. Table.ReplaceValue(#"Last Step",null,"-",Replacer.ReplaceValue,Table.ColumnNames(#"Last Step"))

View solution in original post

12 REPLIES 12
v-cherch-msft
Employee
Employee

Hi @KCinMelbourne

 

The way to replace value in whole table: First select all columns and then use 'Replace value' feature. If it is not your case,could you show us the steps about your issue? I cannot reproduce your scenario.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Cherie. I was looking for a solution that didn't require the columns to be specified (see point 1 in my original message). The reason is that the tables I am retrieving may have columns added or removed. So if you specify a column, it will cause an error when it is removed. Similarly, if a new column is added the find/replace function won't work on that column. So overall, I was wondering if it was possible to perform a find and replace on an entire table rather than on specific columns to overcome this. Cheers Kirsty

Hi @KCinMelbourne

 

I'm afraid there's no better way to achive that. I would suggest you modify or add steps in query editor to replace the values in whole table.

 

Regars,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Maybe you should avoid saying 'there is NO better way to achieve this' unless you are absolutely sure. Clearly, looking at the solution, there is a better way, and exactly what was asked for (using Table.Columns with the name of the previous step). Luckily I didn't just read your answer and give up...I am only saying this as it says you are an 'employee' of Microsoft, so people will take what you say seriously, and in this case it is seriously wrong, despite your assurances...no offence meant, but try not to be so adamant, unless you are absolutely sure.

Thanks. If that's the case, is there a way to use 'try' or similar so that if it tries to replace values in a column and then that column doesn't exist (as it has been deleted), it will skip that column and proceed with the next? Cheers KC

You can do this by using the Table.ColumnNames function in replacement of the specific column list.  E.G. Table.ReplaceValue(#"Last Step",null,"-",Replacer.ReplaceValue,Table.ColumnNames(#"Last Step"))

This is great!

Is it possible to only apply this to columns of type text? I have date and currency columns I would need to bypass.

You should be able to use the Table.ColumnsOfType function in place of the Table.Column to limit the list returned to only columns of a specific type.

Anonymous
Not applicable

Champion! Thanks.

Anonymous
Not applicable

Excellent re whole table thanks!

This solution works and resolves replacing values in columns that can have changing names.

Hi @KCinMelbourne

 

It seems you may have a look at below two articles to check if they could help you.

https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/

https://blog.crossjoin.co.uk/2015/02/26/handling-added-or-missing-columns-in-power-query/

Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors