Replace Value if Column exists

[MarkusEng1998]

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

Is there a way to do the replace on the condition that the column exists?

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

this code return the error "Expression Error: The column 'New List' of the table wasn't found."

Please advise whether this is possible?

Thank you.

[Ahmedx]

pls try this

= Table.ReplaceValue(#"previousStep",null,null,(x,y,z)=>if x =null then "" else x,List.Select( Table.ColumnNames(#"previousStep"), (x)=> x ="New List"))

[MageVortex · Accepted solution]

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

[MarkusEng1998]

Thank you, I will think about the suggestion. I agree to either back out, or ignore the ditch.

Here is some context.

I want to migrate ten columns (old list) and collapse them into two columns (new list). Yet the multiple sources may vary in the number of columns (old list). For each column, I pivot the data to two columns: Old List or New List.  Then I do the compare between old and new lists.

My goal was to set up an audit for this tedious process. There are three desired states to track:

1) just starting (only old list)

2) migrated columns (old list and new list)

3) remove old list columns (only new list)

This is the reason I was trying to define conditions on whether a column exists or not.

[jdbuchanan71]

@MarkusEng1998 

Take a look at MissingField.Ignore paramater.  Here is a video explaining the use with examples.

https://www.youtube.com/watch?v=D59nkSr_JqQ

[MarkusEng1998]

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)