Latest Enterprise DNA Initiatives


Mass capitalized words in query editor

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

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

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

Thanks Keith

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

there is alot of videos on youtube

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

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.

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

1 Like