Forum Discussion
merge partial text rows on condition
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.
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 GroupedRowsAfter 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
4 Replies
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 GroupedRowsAfter 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 RemovedColumnsAnonymous Can you post sample data as text?
You're welcome 🙂
