Forum Discussion
Unpivot table with different fields
Hello everybody,
I have searched and read some posts about pivot and unpivot tables; however, my data is a bit differnet and very ugly, I need some help on the this:
Table I got:
| ID | MonthYear | Team/Score/Minutes | Value 1 | Value 2 | Value 3 |
| a | 202002 | Team | Team X | Team Y | |
| a | 202002 | Score | 5 | 5.7 | |
| a | 202002 | Minutes | 60 | 65 | |
| b | 202001 | Team | Team Z1 | Team Z2 | Team Z3 |
| b | 202001 | Score | 8 | 6 | 6.2 |
| b | 202001 | Minutes | 60 | 55 | 60 |
| b | 202001 | Team | Team Z4 | Team Z5 | Team Z6 |
| b | 202001 | Score | 8.1 | 4.8 | 7 |
| b | 202001 | Minutes | 55 | 70 | 80 |
I would like to transfrom into this:
| ID | MonthYear | Team | Score | Minutes |
| a | 202002 | Team X | 5 | 60 |
| a | 202002 | Team Y | 5.7 | 65 |
| b | 202001 | Team Z1 | 8 | 60 |
| b | 202001 | Team Z2 | 6 | 55 |
| b | 202001 | Team Z3 | 6.2 | 60 |
| b | 202001 | Team Z4 | 8.1 | 55 |
| b | 202001 | Team Z5 | 4.8 | 70 |
| b | 202001 | Team Z6 | 7 | 80 |
I tried to create a new table in DAX with union but didn't succeed (https://community.powerbi.com/t5/Desktop/unpivot-table-with-milestones/m-p/951385#M455873). It seems my case is more dynamic (cuz Team/Score/Minutes) and don't know how to solve it.
Any advice would be appreciated!
5 Replies
Hi @trevorhh9 ,
please paste this code into the advaned editor of a new query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDIwMAIyQlITc6GUQgSMEQlkKMXqoCkNTs4vSgXSpiCsZ45VjW9mXmlJajGQZWYAIkxhqpJgqgzRLI0yhLOM4CxjTD0w2y1AxoKwnhGmIjTrTU0hLPwOMIGzTOEsMzwO0APxTPRADjHH5wSw7eYgd1gAnRALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, MonthYear = _t, #"Team/Score/Minutes" = _t, #"Value 1 " = _t, #"Value 2" = _t, #"Value 3" = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}), #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"ID", "MonthYear", "Index"}, {{"Partition", each Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_, {"ID", "MonthYear", "Index"})))}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"}), #"Expanded Partition" = Table.ExpandTableColumn(#"Removed Columns", "Partition", {"Team", "Score", "Minutes"}, {"Team", "Score", "Minutes"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Partition", each ([Team] <> "")) in #"Filtered Rows"It automatically wraps to more value columns.
Hello ImkeF ,
Thanks for the reply.
I tried to use the query you provided in power bi with the real data. When I did the last step to "#Expanded Partition", it gave the null value for all rows. It seems like I didn't use the column [Team/Scores/Munites] and maybe PBI didn't know what value to put back.
Or am I doing something wrong?
