Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Replace Value if Column exists

I have a query that typically has two columns: "New List" and "Old List." I used the Replacer.ReplaceValue to fix null values.   The issue is that sometimes the query may not have the "New List" c...
Need Help
  • MageVortex's avatar
    MageVortex
    2 years ago

    As an alternative, you could try configuring it differently.  And note, this may fail completely, I don't have a similar situation to try it with readily avialable.

    In the table, in power query editor, create a new column, then set up a formula to make that column pull in values from a column by the name but if it fails, pull in nothing.

    Then do the replacevalue on the values in that column, that way the column always exists and doesn't trigger that error.

    This may not work as the means of filling the column with the occasionaly nonexistent column may trigger that same error  message, but if you use an if else statement, or iferror statement, it should theoretically work....

    Hope that helps

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you jdbuchanan71 for the video link.

     

    However, I am still getting the error message.

    = Table.ReplaceValue(#"previousStep",null, each if Table.HasColumns(#"previousStep", "New List", MissingField.Ignore) = true then "" else [New List], Replacer.ReplaceValue,{"New List"})

     

    When I apply it at the end, the error says it is expecting 5 inputs, not 6.

    = Table.ReplaceValue(#"previousStep",null, each if Table.HasColumns(#"previousStep", "New List") = true then "" else [New List], Replacer.ReplaceValue,{"New List"}, MissingField.Ignore)