Replace Value in Whole Table

[KCinMelbourne]

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

[v-cherch-msft]

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

[KCinMelbourne]

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

[v-cherch-msft]

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

[Bella42]

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.

[KCinMelbourne]

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

[sckienle · 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"))

[kaitlyndunn]

this was EXACTLY what i needed - thank you so much

[HelpWanted]

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.

[sckienle]

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]

Champion! Thanks.

[Anonymous]

Excellent re whole table thanks!

[philipblondewwt]

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

[v-cherch-msft]

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