Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
In the example below, I want to append/merge a certain part of the text of a row when they have the same first two letters.
How can I best do this in powerquery?
Hereby an example below:
Any help/feedback is highly appreciated.
Solved! Go to Solution.
Hi @Anonymous,
for future requests provide sample data as table so we can copy/paste.
Before
After v1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxR0FXITUxPrMrMS1XSUVKK1YlWcvUBCoakFpcYAkVCUitKFPzyFQxRpYyQpIzAUo5ROHX5BBCWwjQQLmWMJGWsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Comment = _t]),
Ad_GroupHelper = Table.AddColumn(Source, "GroupHelper", each Text.Start([Column1], 2), type text),
GroupedRows = Table.Group(Ad_GroupHelper, {"GroupHelper"}, {{"Count", each Text.Combine([Comment], ", "), type text}})
in
GroupedRows
After v2
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxR0FXITUxPrMrMS1XSUVKK1YlWcvUBCoakFpcYAkVCUitKFPzyFQxRpYyQpIzAUo5ROHX5BBCWwjQQLmWMJGWsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Comment = _t]),
Ad_GroupHelper = Table.AddColumn(Source, "GroupHelper", each Text.Start([Column1], 2), type text),
GroupedRows = Table.Group(Ad_GroupHelper, {"GroupHelper"}, {{"Column1", each Text.Combine({[Column1]{0}?} & List.Skip(List.Transform([Column1], (x)=> Text.Trim(Text.AfterDelimiter(x, "-")))), ", "), type text}, {"Comment", each Text.Combine([Comment], ", "), type text}}),
RemovedColumns = Table.RemoveColumns(GroupedRows,{"GroupHelper"})
in
RemovedColumns
Worked like a charm, thank you!
I will include sample data (thank you for the link explaining how to include this).
Hi @Anonymous,
for future requests provide sample data as table so we can copy/paste.
Before
After v1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxR0FXITUxPrMrMS1XSUVKK1YlWcvUBCoakFpcYAkVCUitKFPzyFQxRpYyQpIzAUo5ROHX5BBCWwjQQLmWMJGWsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Comment = _t]),
Ad_GroupHelper = Table.AddColumn(Source, "GroupHelper", each Text.Start([Column1], 2), type text),
GroupedRows = Table.Group(Ad_GroupHelper, {"GroupHelper"}, {{"Count", each Text.Combine([Comment], ", "), type text}})
in
GroupedRows
After v2
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxR0FXITUxPrMrMS1XSUVKK1YlWcvUBCoakFpcYAkVCUitKFPzyFQxRpYyQpIzAUo5ROHX5BBCWwjQQLmWMJGWsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Comment = _t]),
Ad_GroupHelper = Table.AddColumn(Source, "GroupHelper", each Text.Start([Column1], 2), type text),
GroupedRows = Table.Group(Ad_GroupHelper, {"GroupHelper"}, {{"Column1", each Text.Combine({[Column1]{0}?} & List.Skip(List.Transform([Column1], (x)=> Text.Trim(Text.AfterDelimiter(x, "-")))), ", "), type text}, {"Comment", each Text.Combine([Comment], ", "), type text}}),
RemovedColumns = Table.RemoveColumns(GroupedRows,{"GroupHelper"})
in
RemovedColumns
@Anonymous Can you post sample data as text?
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
7 | |
7 | |
6 | |
6 |