Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.