Forum Discussion
Merge / Concatenate Note Fields
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
pettat
If you want to do it in PQ then follow these steps, I made a sampe data setAdded 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"
3 Replies
pettat
If you want to do it in PQ then follow these steps, I made a sampe data setAdded 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"Sorry for the delayed reply - thank you very much!! It worked nicely.
