{"id":1538,"date":"2021-02-12T23:06:21","date_gmt":"2021-02-13T05:06:21","guid":{"rendered":"http:\/\/ninmonkeys.com\/blog\/?p=1538"},"modified":"2022-03-12T10:03:18","modified_gmt":"2022-03-12T16:03:18","slug":"word-wrapping-text-in-power-query","status":"publish","type":"post","link":"https:\/\/ninmonkeys.com\/blog\/2021\/02\/12\/word-wrapping-text-in-power-query\/","title":{"rendered":"Word Wrapping Text in Power Query"},"content":{"rendered":"\n\n\n

How to wrap long lines, *without* splitting words. The input is a very long string, with no newlines:<\/p>\n\n\n\n

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis. Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque. Etiam bibendum dignissim mauris<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Query<\/h2>\n\n\n\n
<a href="https:\/\/docs.microsoft.com\/en-us\/powerquery-m\/list-accumulate">List.Accumulate<\/a><\/code> is an aggregate function. I’m using it to “sum” — to add strings together. If the current line plus the next word is longer than 80 characters, then insert a newline first.<\/p>\n\n\n\n
To find the length of the current line, I only want the length after<\/em> the very last newline. Occurrence.Last<\/code> returns the last match, else -1<\/code> if nothing is found.<\/p>\n\n\n\n
let  \n    LoremIpsum = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis. Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque. Etiam bibendum dignissim mauris",\n\n    \/\/ calculate length of string *after* the rightmost newline\n    Text_LengthAfterNewline = (string as text) as number =>\n        let \n            posLastNewline = Text.PositionOf(string, "#(lf)", Occurrence.Last),\n            posOffset = if posLastNewline <> -1 then posLastNewline else 0,\n            deltaLen = Text.Length(string) - posOffset\n        in\n            deltaLen,\n\n    \/\/ word wraps text\n    Text_WordWrap = (string as text, max_width as number) as text =>\n        let\n            words = Text.Split(string, " "),\n            accum_result = List.Accumulate(\n                words, "", \n                (state as text, current as text) as text =>\n                    let\n                        len = Text_LengthAfterNewline(state) + Text.Length(current) + 1,\n                        maybeNewline =\n                            if len > max_width then "#(lf)" else "",\n\n                        accum_string = Text.Combine({state & maybeNewline, current}, " ")\n                    in \n                        accum_string\n            )\n        in\n            accum_result,\n    \n    wrapped_text = Text_WordWrap(LoremIpsum, 80)\nin\n    wrapped_text<\/code><\/pre>\n\n\n\n
The final result is 80 characters wide or less<\/p>\n\n\n\n
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis.\nCurabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec\nultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque.\nEtiam bibendum dignissim mauris<\/pre>\n\n\n\n
Validating lengths of each line<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n
let\n    Source = #"wrap lines",\n    validate_lengths =\n        let \n            lines = Text.Split(Source, "#(lf)"),\n            lengths = List.Transform(\n                lines,\n                each [ String = _, Length = Text.Length(_) ])\n        in\n            Table.FromRecords(\n                lengths,\n                type table[String = text, Length = number],\n                MissingField.Error )\nin\n    validate_lengths<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Word wrapping text using List.Accumulate<\/code> and Occurrence.Last<\/code>[simple_icon name=\"powerbi\"]<\/p>\n","protected":false},"author":1,"featured_media":1547,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[6,7,74],"_links":{"self":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/1538"}],"collection":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/comments?post=1538"}],"version-history":[{"count":17,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/1538\/revisions"}],"predecessor-version":[{"id":2230,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/1538\/revisions\/2230"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media\/1547"}],"wp:attachment":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media?parent=1538"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/categories?post=1538"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/tags?post=1538"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}