Unpivot table with different fields

[Anonymous]

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!

[Greg_Deckler]

This is a job for @ImkeF or @edhans or someone of similar skill. I think it will involve an index column and a custom column that uses like MOD 3 or something and then you group on it. Not sure but those 2 will be able to help.

[ImkeF]

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.

[Anonymous]

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?

[ImkeF]

Hi @Anonymous 

Yes, you might have to replace the column names in the code.

You can also delete the last steps and do the expansion manually so you can see what's actually there.

[Anonymous]

Hi @ImkeF ,

I found the issue that the column "Team/Scores/Munites" in my read data is not in the third column, so when I expand the table, it showed null as the order is wrong.

Thank you for the brilliant answer!