Forum Discussion

Jaweed's avatar
Jaweed
Helper III
5 years ago
Solved

How to transform a table in PowerBi

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

Need Help
  • ERD's avatar
    ERD
    5 years ago

    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!

4 Replies

    • Jaweed's avatar
      Jaweed
      Helper III
      5 years ago

      Hi ERD

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

       

       

       

      • ERD's avatar
        ERD
        Community Champion
        5 years ago

        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!