How to edit multiple cells in a column

[vat2do]

Hope you have a good day,

I have a column with different values, how i can edit multiple cells in a column using edit query in Power BI

For Example i have below column:

Category

brown

yellow

blue

light blue

brown

light brown

light blue

I need below output:

Category

Brown

Yellow

Blue

Blue

Brown

Brown

Blue

Thanks in Avance

[v-easonf-msft · Accepted solution]

Hi, @vat2do 

If you are not good at coding, you can also try the buttons on the visual interface.

Please try following steps:

1. Select column 'Category' ->go to 'Transform' ->replace Values

Replace Value 'light ' with Value ''

2. go to 'Transform' ->'Format' ->'Capitalize each word'

Result:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-easonf-msft · Accepted solution]

Hi, @vat2do 

If you are not good at coding, you can also try the buttons on the visual interface.

Please try following steps:

1. Select column 'Category' ->go to 'Transform' ->replace Values

Replace Value 'light ' with Value ''

2. go to 'Transform' ->'Format' ->'Capitalize each word'

Result:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vat2do]

Your solution is in DAX or some other language

[ddpl]

In power Query, not in Dax.

[ddpl]

@vat2do In power query first select column and replace "light " with blank 

Then transform the column by category "capitalize each word"

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSirKL89TitWJVqpMzcnJLwczk3JKU8GMnMz0jBIFOBehGCqBzgcrjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","light ","",Replacer.ReplaceText,{"Category"}),
#"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value",{{"Category", Text.Proper, type text}})
in
#"Capitalized Each Word" 

[ddpl · Accepted solution]

@v-rzhou-msft Hi Rico My earlier proposed solution is not worked??