Merge / Concatenate Note Fields

[pettat]

Hi Everyone 

I'm not sure this is possible, but is there a way to merge or concatenate two Note fields

In the attachment, the ParentID is the main order number and the Note field has a maximum length and anything longer than that continues onto the next SeqNum and Note field

This particular example only has SeqNum 1 and 2, but there are others in the database that go much larger (I've seen SeqNum fields as large as 6 for the same ParentID)

I hope that I've explained this well - and thanks in advance for any help you can offer 

[Fowmy · Accepted solution]

@pettat 

If you want to do it in PQ then follow these steps, I made a sampe data set

Added a grouping to combine the notes, the results

Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABFA7JdfkqpgqJCSWpKYmVOsFKsTrWQEkzeCyRuhyIP1GxHQb4RbvzGGfmNM802AhBkW82MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NoteID = _t, #"Parent ID" = _t, #"Other Field" = _t, Note = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NoteID", Int64.Type}, {"Parent ID", Int64.Type}, {"Other Field", Int64.Type}, {"Note", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Parent ID"}, {{"Count", each Text.Combine([Note],", "), type nullable text}})
in
    #"Grouped Rows"

[pettat]

Sorry for the delayed reply - thank you very much!!  It worked nicely.

[tamerj1]

Hi @pettat 
It would be much better if you provide a couple of examples. Thank you