Forum Discussion

pettat's avatar
pettat
Regular Visitor
4 years ago
Solved

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 ...
  • Fowmy's avatar
    Fowmy
    4 years ago

    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"



     




     





Fowmy's avatar
Fowmy
Super User
4 years ago

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's avatar
pettat
Regular Visitor
4 years ago

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