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: A...
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
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