1

In PowerBI when i use find/replace it produces the following: = Table.ReplaceValue(Custom2,"-999","",Replacer.ReplaceText,{"Column1"})

But I'd like to replace -999 in all columns, not just Column1. I'd also like to not have to define a list of all the column names. Is there a way to do this find replace in all columns with a wildcard or something? Any help is much appreciated.

Jess
  • 19
  • 2
  • Similar question: [Easy way to rename multiple columns using either a text function or find & replace](https://stackoverflow.com/questions/74935863/easy-way-to-rename-multiple-columns-using-either-a-text-function-or-find-repla) – chrimaho Dec 28 '22 at 03:12

2 Answers2

1

Try using Table.ColumnNames(). This generates a dynamic list of column names for the table without you having to manually define a list.

= Table.ReplaceValue(#"Table Name","-999","",Replacer.ReplaceText,Table.ColumnNames((#"Table Name" as table)))

Rory
  • 413
  • 1
  • 4
  • 16
0

You're looking for the Table.ColumnNames() function.

For example, we can transform this table:

col1 col2 col3
1 a x
2 b y
3 c z

In to this:

col1 col2 col3
1 a x
2 y
3 c z

By using this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4gqlWJ1oJSMgKwmIK8E8YyArGYirlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"b","",Replacer.ReplaceText,Table.ColumnNames(Source))
in
    #"Replaced Value"
chrimaho
  • 580
  • 4
  • 22