Replacing the long text column with text in Power Query?

[Anonymous]

Hi, I am working on survey data. I have a long questions column and would like to replace it with shorter words

Example of my column

What are some skills that are in high demand in your current & future plan? Please rank the following categories. - Machine Learning Engineer

What are some skills that are in high demand in your current & future plan? Please rank the following categories. - Machine Learning Engineer

What are some skills that are in high demand in your current & future plan? Please rank the following categories. - Machine Learning Engineer

I want to replace the above questions in "Skills". I have 25 questions similar to the above. I tried to use the IF condition but the long questions are not replacing. Also, I tried using replace by right click the column in the Power query, again it is not replacing. I can able to replace it if my questions are shorter.

Any suggestions? Do we need to use the M language? Please advise

[v-yalanwu-msft]

Hi， @Anonymous ;

You could add conditional column then delete original coulmn.

The final output is shown below:

you could delete the question column.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous that tells me the question on different rows doesn't have the same value, there might be some minor changes, which I cannot tell. In the example you gave me, when I right clicked on the actual question value, it replaced all 3 questions coz they are the same. I think we are going in circles for no reason, you need to check and confirm your data.

If you simple make copy of your table, remove other columns, right click on the column header and remove duplicate values and then sort, check if the same question has only one value or it shows multiple values, which will tell it is not a distinct value.

[parry2k · Accepted solution]

@Anonymous so you right-clicked on the actual question value, not on the column header, and then selected replace value?

[Anonymous]

@parry2k No I right-clicked the column header as I have the same questions presents in the column multiple times. When I right-clicked the actual questions, it works. However, it is not replacing all the same questions in the column

[parry2k]

@Anonymous did you check "match entire cell contents"

[Anonymous]

yes @parry2k 

This is my below M Code

= Table.ReplaceValue(#"Cleaned Text1","Are you actively looking for permanent or fixed-term employees to fill vacancies in any of the following areas? - Architects -Employment Type","Skills in demand",Replacer.ReplaceValue,{"Custom"})

Is there any other way available? My data comes from SurveyMonkey

[parry2k]

@Anonymous I just right-clicked the question and gave the replaced value and it worked:

screen shot after replace step:

M Query script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7U67CoNAEPyVwToJkiKtVSBFAulSGItF1/Pw3JO9O8S/z1nkAwKWaQbmwczUdfEaKIKUEfyUYbTOBcSvaAWDNQM6nki6ja4+KdqkyhLxTmV5vqBPMeXw7EgqPB1TYCjJmHsYvXfOL1YMWopsvFoOJxzxoHawwrgzqWz2VUzmrEVz+N/67dbNL8iTK3ianV+ZQ7WTjsXrmPd28JsP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Questions = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Questions", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","What are some skills that are in high demand in your current & future plan? Please rank the following categories. - Machine Learning Engineer","Skills",Replacer.ReplaceValue,{"Questions"})
in
    #"Replaced Value"

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k  I followed the same steps. But it's not replacing. I cleaned and trimmed the column before replacing it. Not sure what would be the issue. 

I can able to replace shorter questions. 

[parry2k]

@Anonymous not actually clear what you are looking for? Can you provide bit more details?

[Anonymous]

Hi @parry2k Sorry for not be clear

I have a column called 'Questions'

I want to replace these questions with shorter words and below is the output I am expecting

I can able to replace the shorter questions, however, I am not able to replace the first three long questions into "Skills"