CODEWITHSUNDEEP
powerbipowerquery
mrbungle
mrbungle

Reputation: 1931

pbi m query to concatenate a field

Not well versed in PBI formulas but kinda know what I'm looking for. I have a comment field that can be long so what I'm trying to do is

if text.length([comment] > 45) then text.range(text.combine([comment],"..."),45) else [comment]

Some of the comments field will be null as well. I've tried different variations of this and just can't see to get it right. Appreciate any help. Thanks.

Upvotes: 0

Views: 87

Answers (1)

horseyride
horseyride

Reputation: 21373

Note, M is case sensitive, so text.length() Null and text.combine will not work. You have to use Text.Length() , Text.Combine() , null, etc.

Try this:

= try if Text.Length([comment])>45 then Text.Start([comment],45)&"..." else [comment] otherwise null

You can use add that as part of a new custom column:

let Source = #table({"comment"},{{"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"},{"Bus"},{null}, {"Car Log"}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"comment", type text}}),
#"Add Custom Column" = Table.AddColumn( #"Changed Type", "Custom", each try if Text.Length([comment])>45 then Text.Start([comment],45)&"..." else [comment] otherwise null)
in  #"Add Custom Column"

Or transform the existing column:

let Source = #table({"comment"},{{"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"},{"Bus"},{null}, {"Car Log"}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"comment", type text}}),
#"Transform Column" = Table.TransformColumns(#"Changed Type",{{"comment", each try if Text.Length(_)>45 then Text.Start(_,45)&"..." else _ otherwise null, type text}})
in  #"Transform Column"

Upvotes: 1

Related Questions