Duplicate row values to columns

[stanislav]

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 · Accepted solution]

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"

[IbrarKaleem1]

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

[stanislav]

Thank you very much! It works for me.

Regards,

Stan