Forum Discussion
Duplicate column - new column is lowercase text (Collect, Combine and Transform Data - Gil Raviv
Hay peeps,
I am just doing chapter 11 in the Gil Raviv as I need to do some text anaylsis and was reviewing his tips.
Step 1 is to duplicate the Message column and the rational for this is to create a lower case version.
Step 2 is to then create a conditional column to extract the key words in the exersize, from lower case to Sentance Case.
However, upon doing this and reviewing the column, the duplicated column is in fact an exact copy and the text is not in lowercase as suggested in the book.
So, has something changed since the book was relased? or is this the expected behaviour - that the column is an exact copy.
Just clarifying as this is going to create problems when following the exerice, but also I need to understand how I will do this with the actual data when I start my analysis.
Any advice greatfully recieved, I have done some searching but many posts and blogs are a fair few years old now.
I solved this issue, as I was rushing ahead and not following step by step and using different ways to get the end result (as I am confident in M and PQ) I missed the step that says to select the column and use the UI to turn all text to lowercase! Doh! (facepalm)
3 Replies
To convert the text in the column to lowercase, you can use the "Text.Lower" function in Power Query Editor. Here's how:
- Right-click on the duplicate column and select "Transform Column"
- Select "Custom Column"
- In the custom column formula, type "Text.Lower([YourColumnName])"
- Replace "YourColumnName" with the actual name of the duplicate column.
- Press OK to apply the changes
This will create a new column with the text in lowercase.
MAwwad
Thanks for your reply, I am aware that you can convert text with M functions, I was just puzzled by the exercise in the book.
The book states:
"Because Power Query is case sensitive in searches, in the following steps you will duplicate the Message column and work on the duplicate copy, where all the text is lowercase. You will keep the original messages intact for cases in which you should include the original text in your report to maintain the specific uppercased characters in the messages." Chapter 11 pp 279So I was a little perplexed, as the case did not change to lowercase, in the duplicated column. Therefore, there is no real need to duplicate the column if you wanted to key word the messages for catogorisation. As you suggest you can duplicate the column and then edit the M code to change the case to lowercase or use the UI to do so on the column.
I solved this issue, as I was rushing ahead and not following step by step and using different ways to get the end result (as I am confident in M and PQ) I missed the step that says to select the column and use the UI to turn all text to lowercase! Doh! (facepalm)
