Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Sorry for the delayed reply - thank you very much!! It worked nicely.
Hi @pettat
It would be much better if you provide a couple of examples. Thank you
User | Count |
---|---|
47 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
55 | |
28 | |
20 | |
15 |