Mass capitalized words in query editor

Power Query
1

Hi all,

How do I mass capitalize each word in Power Query Editor. Can I do the below image in one step with a query formula? If so, can someone direct me how?

Much appreciated.

image

2

if i remember correctly, Mellisa did a video on something like that. Check out her videos on youtube and learning portal

3

I know she did it on replace but let me see if she did for transform.

Thanks Keith

4

when you transform and close the query to update the table in power bi

5

there is alot of videos on youtube

6

Hi @ysherriff,

Here you go. Just copy the sample into a new blank query. This will transform all of the columns in your table at once.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8iotLlEozs9NVShKzEvJz1VISSxJVNJRAnL09PQgMrn5RalKsTrRSoZGxiamFkDZ/CKFRIXk/NykTAVDXSNdY4X87MRKe6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    AllCaps = Table.TransformColumns( Source, {}, each Text.Upper(_) )
in
    AllCaps

.
If you have other data types than text in any of the columns, add a try-otherwise clause to handle subsequent type mismatch errors by returning the initial value for example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8iotLlEozs9NVShKzEvJz1VISSxJVNJRAnL09PQgMrn5RalKsTrRSoZGxiamFkDZ/CKFRIXk/NykTAVDXSNdY4X87MRKe6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    AllCaps = Table.TransformColumns( Source, {}, each try Text.Upper(_) otherwise _ )
in
    AllCaps

I hope this is helpful.

2 Likes
7

Thanks Melissa.

Did you create a video for this? If so, I have not gotten to it yet. Trying to finish all my courses with my current workload.

Much appreciated.

8

Yep this technique is covered in the “Applied Problem Solving with Power Query/M” course, chapter Bulk Find and Replace.

1 Like