Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
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
Thank you very much! It works for me.
Regards,
Stan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |