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 everybody
Can Anybody assist me? I have a table in Power Bi that I uploded. Typical records are as below.
Company Country-Deal-with
Cpy1 USA
Cpy1 France
Cpy1 Belgium
Cpy2 UK
CPy2 France
CPy3 India
CPY2 USA
I would like to have one record per company with different columns. I do not know if PBi can handle.
Company Col 1 Col2 Col3 Col-combined
Cpy1 USA France Belgium USA France Belgium
Cpy2 UK France UK France
Cpy3 India USA India USA
I would highly appreciate your help. Thanks
Solved! Go to Solution.
@Jaweed ,
Here are the detailed steps:
1. Create a group index (how-to: How to create group index with Power Query or R)
2. Pivot columns (how-to: Pivoting columns that can't be aggregated)
3. Add a custom column for the merged values:
Text.Combine(List.RemoveNulls({[1],[2],[3]}), " ")
All steps in code:
#"Sorted Rows" = Table.Sort(Source,{{"Category", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Category"}, {{"Group", each _, type table [Category=nullable text, Country=nullable text]}}),
#"Indexed" = Table.TransformColumns(#"Grouped Rows", {{"Group", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
#"Expanded Group" = Table.ExpandTableColumn(Indexed, "Group", {"Country", "GroupIndex"}, {"Group.Country", "Group.GroupIndex"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Group",{"Category", "Group.GroupIndex", "Group.Country"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"Group.GroupIndex", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"Group.GroupIndex", type text}}, "en-US")[Group.GroupIndex]), "Group.GroupIndex", "Group.Country"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Col-Combined", each Text.Combine(List.RemoveNulls({[1],[2],[3]}), " "))
Did I answer your question? Mark my post as solution!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @Jaweed
You may refer to this community blog: Rows and columns conversion and bulk renaming of c... - Microsoft Power BI Community. It deals with a similar request.
Regards,
Community Support Team _ Jing
Hi @Jaweed ,
please have a look at the article below:
https://docs.microsoft.com/en-us/power-query/pivot-columns
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@Jaweed ,
Here are the detailed steps:
1. Create a group index (how-to: How to create group index with Power Query or R)
2. Pivot columns (how-to: Pivoting columns that can't be aggregated)
3. Add a custom column for the merged values:
Text.Combine(List.RemoveNulls({[1],[2],[3]}), " ")
All steps in code:
#"Sorted Rows" = Table.Sort(Source,{{"Category", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Category"}, {{"Group", each _, type table [Category=nullable text, Country=nullable text]}}),
#"Indexed" = Table.TransformColumns(#"Grouped Rows", {{"Group", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
#"Expanded Group" = Table.ExpandTableColumn(Indexed, "Group", {"Country", "GroupIndex"}, {"Group.Country", "Group.GroupIndex"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Group",{"Category", "Group.GroupIndex", "Group.Country"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"Group.GroupIndex", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"Group.GroupIndex", type text}}, "en-US")[Group.GroupIndex]), "Group.GroupIndex", "Group.Country"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Col-Combined", each Text.Combine(List.RemoveNulls({[1],[2],[3]}), " "))
Did I answer your question? Mark my post as solution!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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.