How to transform a table in PowerBi

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!