Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 @PowerBI_Consult,
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 @PowerBI_Consult,
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
@PowerBI_Consult Can you post sample data as text?