How to transform a table in PowerBi

[Jaweed]

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

[v-jingzhang]

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

[ERD]

Hi @Jaweed ,

please have a look at the article below:

https://docs.microsoft.com/en-us/power-query/pivot-columns 

[Jaweed]

Hi @ERD

Thanks, Tried the pivot, but did not get the expected results. I will have alook once more.

[ERD · Accepted 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!