Forum Discussion
wad11656
Advocate I
Advocate INew column with concatenated values based on 2 matching IDs
In Power Query, How can I create a new column that combines the values from 1 column, if the values in 2 columns match? In this case, how can I concatenate Contact_Type values when the Contact_ID...
Hi wad11656 ,
Try this, using grouping but and you don't have to worry for the remaning columns (you cen even add columns without changing the code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS2K93RR0lFyzs8rSUwuQeWEVBakInH9EnNBXJfS3NzKeJfEkkSlWJ1oJUOgkAkQB3t4BgR4+rkDmb6p6Yl5QNrQyNgERY2Tp48PDiVGYA6qOQGpJakYasxRzAnKT0JWYUyEY4xhNiFMQbEoFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Order_ID", "Contact_ID"}, {{"Rows", each _, type table [Order_ID=nullable text, Contact_ID=nullable text, Contact_Type=nullable text, Contact_Name=nullable text, Dummy_Data=nullable text]}}), Custom1 = Table.TransformColumns(#"Grouped Rows",{{"Rows", (Group)=> Table.AddColumn(Group, "Contact_Type-mod", each Text.Combine(Group[Contact_Type], " & "))}}), Custom2 = Table.Combine(Custom1[Rows]) in Custom2
latimeria
Solution Specialist
Solution SpecialistHi wad11656 ,
Try this, using grouping but and you don't have to worry for the remaning columns (you cen even add columns without changing the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS2K93RR0lFyzs8rSUwuQeWEVBakInH9EnNBXJfS3NzKeJfEkkSlWJ1oJUOgkAkQB3t4BgR4+rkDmb6p6Yl5QNrQyNgERY2Tp48PDiVGYA6qOQGpJakYasxRzAnKT0JWYUyEY4xhNiFMQbEoFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"Order_ID", "Contact_ID"}, {{"Rows", each _, type table [Order_ID=nullable text, Contact_ID=nullable text, Contact_Type=nullable text, Contact_Name=nullable text, Dummy_Data=nullable text]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows",{{"Rows", (Group)=> Table.AddColumn(Group, "Contact_Type-mod", each Text.Combine(Group[Contact_Type], " & "))}}),
Custom2 = Table.Combine(Custom1[Rows])
in
Custom2
wad11656Advocate I
Advocate IThank you - that seemed to have worked great.
Unfortunately another reason I don't like Grouping is because each time I implement a new Grouping, it seemingly adds 30 minutes to 1hr of report loading time. So now I am stuck waiting forever for my data. Microsoft should optomize their grouping algorithm.