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: IDTypeProgramsNameID1AMEnglishAMID1-nameID2AMFrenchAMID2-nameID3PMArabicPMID3-nameID1PMSpanishPMID1-nameID2PMCoreanPMID2-nameID1AMItalianAMID1-nameID2AMEnglishAMID2-nameID3PMItalianPMID3-nameID1AMFrenchAMID1-name After merging, I would like to obtain the table below. IDType-ProgramsNameID1AM - EnglishAM, ItalianAM, FrenchAMPM - SpanishPMID1-nameID2AM - FrenchAM, EnglishAMPM - CoreanPMID2-nameID3PM - ArabicPM, ItalianPMID3-name Can somebody pls help?Thank you

Forum Discussion

Helper II

6 years ago

Solved

Create a list field by merging linked fields

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

Tips and Tricks

Tutorial Requests

Anonymous

6 years ago

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

10 Replies

Fowmy

Super User

6 years ago

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

Helper II

6 years ago

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

Super User

6 years ago

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

Anonymous

Not applicable

6 years ago

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"

ziying35

Impactful Individual

6 years ago

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

Not applicable

6 years ago

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

Not applicable

6 years ago

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

Not applicable

6 years ago

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"

Icey
Community Support
6 years ago
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
  Helper II
  6 years ago
  Thank you all for all the solutions.
  It is very helpful and instructive to explore alternatives.
  Best.

Forum Discussion

Create a list field by merging linked fields

10 Replies

Related content

Merge statement creates duplicates - Low shuffle merge as cause?

Semantic Link Lab - Create Data Model

Table Merge creates duplicate records

MERGE QUERIES

FABRIC and DataVerse : Create de Fabric Link