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           ...
  • 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"

     

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"

 

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