CODEWITHSUNDEEP
powerbidaxpowerquery
Jess
Jess

Reputation: 19

How to find & replace in all columns with Power BI

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.

Upvotes: 1

Views: 1658

Answers (2)

chrimaho
chrimaho

Reputation: 684

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"

Upvotes: 0

Rory
Rory

Reputation: 413

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)))

Upvotes: 1

Related Questions