Replacing a specific value in ALL cells, without having to specify column names?

[Pointbreak5]

I'm dealing with a single table / CSV file where they used the '-' character as a replacement for lack of value (instead of leaving it empty). I guess it's meant to stand for Not Applicable. It throws off PowerBI since it can't apply a numerical column type when this character is detected in a column that is otherwise numbers.

I can't fix the data at the source, and repairing it locally doesn't help since my goal is sharing my PowerBI reports with people who will point it at their own local data which they can't be expected to repair.

I'd like to perform a search-and-replace on the entire table during the data import step, prior to assigning a data type to the columns. I figured out how to do it if I explicitly specify the column names:

= Table.ReplaceValue(#"PreviousStep","-","",Replacer.ReplaceValue,{"Source.Name", "Column 1", "Column 2 ", "Column N"})

...but I don't want to make assumptions about column names in the step, because they can differ depending on the version of the tool that produced this CSV file. Plus there's about 500 columns in the CSV sample I'm working with.

So how can I rewrite the above in a way that enumerates over the column names without explicitly naming them?

[dm-p · Accepted solution]

Hi @Pointbreak5,

If you want to do this completely indiscriminately across all columns in your query, just use Table.ColumnNames to pass in the list of all columns from #PreviousStep, where you're passing your hard-coded column names, e.g.:

= Table.ReplaceValue(#"PreviousStep","-","",Replacer.ReplaceValue,Table.ColumnNames(#"PreviousStep"))

 Here's a simple table I have to hand:

I can then use this to replace all occurences of 'E' with '___' across all columns, e.g.:

Hopefully this is all you need. Good luck!

Daniel

---

If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂