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.
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 | ID1-name |
ID2 | AM - FrenchAM, EnglishAM | ID2-name |
ID3 | PM - ArabicPM, ItalianPM | ID3-name |
Can somebody pls help?
Thank you
Solved! Go to 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
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
Thank you all for all the solutions.
It is very helpful and instructive to explore alternatives.
Best.
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"
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
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"
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
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"
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.