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

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

KCinMelbourne's avatar
KCinMelbourne
Icon for Resolver I rankResolver I
7 years ago
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's avatar
    sckienle
    Icon for Advocate II rankAdvocate II
    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"))

    • philipblondewwt's avatar
      philipblondewwt
      New Member
      7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Excellent re whole table thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Champion! Thanks.

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

    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