none
Editing Individual Values using the Query Editor RRS feed

  • Question

  • Hi all,

    As part of our data cleansing we sometimes need to change the values of individual cells, i.e. due to obvious data entry mistakes such as 100kg instead of 10kg. Every time the data is refreshed these operations need to be completed. I understand how to replace values in particular columns, but is it possible to replace values for a particular column AND row? Does this need to be done through manipulating the Power Query code? If so, what would the code look like?

    Thanks very much for any help!

    Ralph

    Tuesday, January 5, 2016 3:59 PM

Answers

  • Since the requirement is to replace the value in a particular column and row, we can assume that no global replacement solution will work. A value of 100kg might be valid in one row but be invalid in another row in the same column. The best solution is to use the information to correct the invalid entry at the data input source.

    However, if the data cannot be changed at source, and since the error is specific to row and column entries, the data source must have a key column, otherwise there's no way to track individual rows.

    If a key column is available, then I'd recommend building and exception table that contains key and replacement value columns. Then merge the source table with the exception table, using a left outer join. After expanding the resulting table column, there will be mostly null values in the replacement column (except for the matched rows containing the replacement values). Next, create a new custom column with a formula like:

    if [<replacementcolumnName>] = null then [<originalDataColumnName>] else [<replacementcolumnName>]

    • Marked as answer by rmyers2 Monday, January 11, 2016 9:35 AM
    Tuesday, January 5, 2016 7:39 PM

All replies

  • What is your data source, can rows change order? Is there a primary key?
    Tuesday, January 5, 2016 6:43 PM
    Moderator
  • Are you able to define translation rules or a translation table? Then PQ can do it:

    http://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/

    or if then statements in a new column (if [Column1] = "100kg" then "10kg" else if some more... else [Column1].


    Imke Feldmann TheBIccountant.com

    Tuesday, January 5, 2016 6:48 PM
    Moderator
  • Since the requirement is to replace the value in a particular column and row, we can assume that no global replacement solution will work. A value of 100kg might be valid in one row but be invalid in another row in the same column. The best solution is to use the information to correct the invalid entry at the data input source.

    However, if the data cannot be changed at source, and since the error is specific to row and column entries, the data source must have a key column, otherwise there's no way to track individual rows.

    If a key column is available, then I'd recommend building and exception table that contains key and replacement value columns. Then merge the source table with the exception table, using a left outer join. After expanding the resulting table column, there will be mostly null values in the replacement column (except for the matched rows containing the replacement values). Next, create a new custom column with a formula like:

    if [<replacementcolumnName>] = null then [<originalDataColumnName>] else [<replacementcolumnName>]

    • Marked as answer by rmyers2 Monday, January 11, 2016 9:35 AM
    Tuesday, January 5, 2016 7:39 PM