Forum Discussion

stanislav's avatar
stanislav
New Member
8 years ago
Solved

Duplicate row values to columns

Hi All,

 

I'm relatively new to PowerBI Desktop and not familiar with all its functionality. Hopefully community can help me to resolve the issue I faced with.

 

I have a table

 

KEY                |          VALUE

-------------------------------

id                   |             1

name             |            John

department   |              IT

id                   |              2

name             |            Scott

department   |            Sales

id                   |              3

name             |           Michael

department   |            Sales

 

 

The values in the KEY column are repeatable and I want to move these values into column names, like

 

    id         |          name      |      departament

-------------------------------------------------

    1          |         John         |      IT

    2          |         Scott        |       Sales

    3          |         Michael    |       Sales

 

I've tried to achieve this with grouping and pivoting but no success so far, so I will appriciate any help.

 

Regards,

Stan

  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    The trick is to first add a temporary Index column (starting with 0; Add Column Tab - Index Column) and transform that column by Intger-Dividing (Transform tab - Standard - IntegerDivide) the values by 3, so you get a column with 0,0,0,1,1,1,2,2,2.

     

    Now you can pivot with advanced option "Don't aggregate" and do some finishing touches:

     

    let
    Source = Table,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[KEY]), "KEY", "VALUE"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"id", Int64.Type}, {"name", type text}, {"department", type text}})
in
    #"Changed Type"

     

3 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    The trick is to first add a temporary Index column (starting with 0; Add Column Tab - Index Column) and transform that column by Intger-Dividing (Transform tab - Standard - IntegerDivide) the values by 3, so you get a column with 0,0,0,1,1,1,2,2,2.

     

    Now you can pivot with advanced option "Don't aggregate" and do some finishing touches:

     

    let
    Source = Table,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[KEY]), "KEY", "VALUE"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"id", Int64.Type}, {"name", type text}, {"department", type text}})
in
    #"Changed Type"

     

    • stanislav's avatar
      stanislav
      New Member
      8 years ago

      Thank you very much! It works for me.

       

      Regards,

      Stan

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello MarcelBeug ,

       

      Thank you for this technique. Quite useful. But a small question, what if the sets are not in multiple of 3. So for example, if the data looks like below?


      Key | Value
      ---------------------
      id | 1
      Name | John
      Department | IT
      country | USA
      id | 2
      Name | Scott
      Department | Sales
      id | 3
      Name | Michael

       

      How this can be catered, i have a data set with similar issue:

      Here is the link to dropbox: https://www.dropbox.com/scl/fo/dra5t6k9aeroqova2c9d9/h?rlkey=usqsvy155xfqi6djq32ns6ptz&dl=0

       

      Much appreciate your knowledge sharing.

       

      Thanks