Create a list field by merging linked fields

[Merleau]

Hello,

I have a table with multiple columns (more than 30) in no particular order. I need to merge 2 of these columns. For example, the original table is:

ID	Type	Programs	Name
ID1	AM	EnglishAM	ID1-name
ID2	AM	FrenchAM	ID2-name
ID3	PM	ArabicPM	ID3-name
ID1	PM	SpanishPM	ID1-name
ID2	PM	CoreanPM	ID2-name
ID1	AM	ItalianAM	ID1-name
ID2	AM	EnglishAM	ID2-name
ID3	PM	ItalianPM	ID3-name
ID1	AM	FrenchAM	ID1-name

After merging, I would like to obtain the table below.

ID	Type-Programs	Name
ID1	AM - EnglishAM, ItalianAM, FrenchAM PM - SpanishPM	ID1-name
ID2	AM - FrenchAM, EnglishAM PM - CoreanPM	ID2-name
ID3	PM - ArabicPM, ItalianPM	ID3-name

Can somebody pls help?

Thank you

[Icey]

Hi @Merleau ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[Merleau]

Thank you all for all the solutions.

It is very helpful and instructive to explore alternatives.

Best.

[Anonymous]

there seems to be a lot of solutions to choose from.

Here is another one

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKqSxIBVIBRfnpRYm5xUCmX2JuqlKsDkjeEMh19AUSrnnpOZnFGWA2UFg3D6HGCKbGrSg1LxmmxAhZiTHIApC4Y1FiUmZyAESJMbISQ5iS4ILEPKBNAThsAos75xelJuYFYLEJ7mDPksSczMQ8vA5G9RR2F0PNweVkTI/DrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID"}, {{"progrs", each List.Sort(List.Transform(_[Programs],each Text.End(_,2)&"-"&_))}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"progrs", each Text.Replace(Text.Replace(Text.Replace(Text.Combine(_,","),"AM,P","AM#(cr)P"),"AM-",""),"PM-","")})
in
    #"Extracted Values"

[Anonymous · Accepted solution]

a shorter one which also uses the Type column, whose presence I had not yet noticed 😀

let
    Source = Table.FromRecords (Json.Document (Binary.Decompress (Binary.FromText("i65W8nRRsgIShko6SiGVBalAjqMvkB1QlJ9elJhbDOS75qXnZBZngIX9EnNTIep180DMWh24CUa4TXArSs1LRjPACMMAY4QBAWgGOBYlJmUmB6AYYIxhgCFuA4ILEvOAfggg3g/oJjjnF6Um5gUQ8AOeYPQsSczJTMyjIBixRgRJ4Qh1AwkBSUxUwjwRCwA=",BinaryEncoding.Base64),Compression.Deflate))),

    group = Table.Group(Source,"ID", {"Type-Programs", each Text.Combine(List.Transform(Table.Group(_, "Type", {"_", each Text.Combine(_[Programs], ", ")})[_],each "-"&_),"#(cr)")})
in
    group

[Anonymous]

and this the solution derived from imke solution to the other similar problem:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRcvQFEq556TmZxRlgNlBYNy8xN1UpVgekxgimxq0oNS8ZpsQIWYkxUCgAJO5YlJiUmRwAUWKMrMQQpiS4IDEPaFMADpvA4s75RamJeQFYbII72LMkMSczMQ+vg1E9hd3FUHNwORnT44aYSnQDckqLgSz3otTUbAQXoTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Programs = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}, {"Programs", type text}, {"Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Programs",each  Text.Combine(_,",")),
    #"Inserted Merged Column" = Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine({[PM], [AM], [#"AM-Plus"]}, "#(cr)"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"AM", "PM", "AM-Plus"})
in
    #"Removed Columns"

[ziying35]

@Merleau 

let
    Source = Table.FromRecords (Json.Document (Binary.Decompress (Binary.FromText("i65W8nRRsgIShko6SiGVBalAjqMvkB1QlJ9elJhbDOS75qXnZBZngIX9EnNTIep180DMWh24CUa4TXArSs1LRjPACMMAY4QBAWgGOBYlJmUmB6AYYIxhgCFuA4ILEvOAfggg3g/oJjjnF6Um5gUQ8AOeYPQsSczJTMyjIBixRgRJ4Qh1AwkBSUxUwjwRCwA=",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (tbl)=>
       let grpinn = Table.Group(tbl, "Type", {"tt", each Text.Combine([Programs], ", ")})
       in  Text.Combine(Table.ToList(grpinn, each _{0}&"-"&_{1}), "#(lf)"),
    group = Table.Group(Source, {"ID", "Name"}, {"Type-Programs", fx })
in
    group

[Anonymous]

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKqSxIBVIBRfnpRYm5xUCmX2JuqlKsDkjeEMh19AUSrnnpOZnFGWA2UFg3D6HGCKbGrSg1LxmmxAhZiTHIApC4Y1FiUmZyAESJMbISQ5iS4ILEPKBNAThsAos75xelJuYFYLEJ7mDPksSczMQ8vA5G9RR2F0PNweVkTI/DrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID"}, {{"progrs", each List.Sort(_[Programs],(x)=>Text.End(x,2))}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"progrs", each let rigth=Text.AfterDelimiter(Text.Combine(_, ","),"AM,",{0,RelativePosition.FromEnd}) in Text.BeforeDelimiter(Text.Combine(_, ","),rigth)&"#(cr)"& rigth})
in
    #"Extracted Values"

[Fowmy]

@Merleau 

Paste the code in a blank query and check the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKqSxIBVIBRfnpRYm5xUCmX2JuqlKsDkjeEMh19AUSrnnpOZnFGWA2UFg3D6HGCKbGrSg1LxmmxAhZiTHIApC4Y1FiUmZyAESJMbISQ5iS4ILEPKBNAThsAos75xelJuYFYLEJ7mDPksSczMQ8vA5G9RR2F0PNweVkTI/DrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID"}, {{"Count", each _, type table [ID=nullable text, Type=nullable text, Programs=nullable text, Name=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Programs]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Custom.1", each [Count][Name]{0}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count"})
in
    #"Removed Columns"

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Merleau]

Hi @Fowmy 

Thanks so much for the prompt response. This is helping me move towards the final goal.

Now, I need to sort the custom column and divide it into the two type"AM" and "PM", possibly using line break.

Thsi is a strong requirement that must be fulfilled.

I will try to get it right using your solution.

Thanks.

[Fowmy]

@Merleau 

Hope this is what you are expecting:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKqSxIBVIBRfnpRYm5xUCmX2JuqlKsDkjeEMh19AUSrnnpOZnFGWA2UFg3D6HGCKbGrSg1LxmmxAhZiTHIApC4Y1FiUmZyAESJMbISQ5iS4ILEPKBNAThsAos75xelJuYFYLEJ7mDPksSczMQ8vA5G9RR2F0PNweVkTI/DrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID", "Name", "Type"}, {{"Program", each Text.Combine([Programs],"#(lf)"), type nullable text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"ID", Order.Ascending}, {"Type", Order.Ascending}})
in
    #"Sorted Rows"

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply

YouTube, LinkedIn