Loading...

PowerBI - Power Query/M - Limit characters on column value and add suffix


Background: I have a column with email subject. I want these to be maximum 30 characters long. For the user to spot that i have cut the ones over 30 characters, i want to add a "..." suffix.

Problem: If the column content is over 30 characters, i want to remove all characters over 30, and add "..." to the end of the string.

What i have tried: I have added the following steps in the Power Query Editor, but it adds "..." to all lines, also the ones under 30 characters.

#"Extracted First Characters" = Table.TransformColumns(#"Duplicated Column", {{"subject - Copy", each Text.Start(_, 30), type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Extracted First Characters",{{"subject - Copy", "subject - short"}}),
#"Added Suffix" = Table.TransformColumns(#"Renamed Columns1", {{"subject - short", each _ & "...", type text}}),

Thanks in advance

- - Source

Answers

answered 1 week ago Olly #1

You can transform the subject column, in one step:

= Table.TransformColumns(#"Previous Step", {{"Subject", each if Text.Length(_) > 30 then Text.Start(_, 30) & "..." else _, type text}})

We test if the text length is greater than 30 characters, and if so, return only the first 30 characters suffixed by "...", otherwise just return the text as is.

comments powered by Disqus