Forum Discussion

KCinMelbourne's avatar
KCinMelbourne
Icon for Resolver I rankResolver I
7 years ago
Solved

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

Need Help
  • sckienle's avatar
    sckienle
    7 years ago

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

13 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      KCinMelbourne
      Icon for Resolver I rankResolver I
      7 years ago
      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's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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