Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
83 | |
81 | |
72 | |
72 | |
55 |
User | Count |
---|---|
108 | |
105 | |
96 | |
86 | |
68 |