Transform table with double unpivot

[Jeremy19]

Hi, 

I have :

Player    Pass   %Pass     Tackle    %Tackle   Game

A           10         0.8           5             0.7         G1

B            15        0.9           7             0.8         G1

A           12         0.7           6             0.9        G2

B           10         0.5           10           0.6         G2

And I need to have :

Player     Game    %Action   Value       Count     Value

A               G1       %Pass       0.8          Pass        10

A               G1       %Tackle    0.7          Tackle     7

B              G1        %Pass       0.9           Pass       15

...

I was thinking of using unpivot the columns but I cant seem to get what I want, do you know how I could do it?

Thanks

[camargos88 · Accepted solution]

Hello @Jeremy19 ,

Check this mcode:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABIGehZA0hTMMgeS7oZKsTrRSk4geYioJZA0h6uEyoP1G8F1mcFVuhsh9EPMNwUzFcBsM6iKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Player = _t, Pass = _t, #"%Pass" = _t, Tackle = _t, #"%Tackle" = _t, Game = _t]),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"Player", "Game", "Pass", "%Pass", "Tackle", "%Tackle"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Player", "Game"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.Join(
                            Table.AddIndexColumn(Table.SelectRows(#"Unpivoted Other Columns", each not Text.StartsWith([Attribute], "%")), "Index", 1,1), {"Index", "Player", "Game"},
                            Table.AddIndexColumn(Table.RenameColumns(Table.SelectRows(#"Unpivoted Other Columns", each  Text.StartsWith([Attribute], "%")), {{"Attribute", "%Action"}, {"Value", "Value2"}}), "Index", 1,1), {"Index", "Player", "Game"}
                        ),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

[v-deddai1-msft · Accepted solution]

Hi @Jeremy19 ，

camargos88 has provided a good solution to you, but it  include custom m query.  I have provided a way to do this through the interface in the Query Editor(You don't have to learn about m query)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABIGehZA0hTMMgeS7oZKsTrRSk4geYioJZA0h6uEyoP1G8F1mcFVuhsh9EPMN0UwzaAKYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Player " = _t, Pass = _t, #"%Pass" = _t, Tackle = _t, #"%Tackle" = _t, Game = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Player ", type text}, {"Pass", Int64.Type}, {"%Pass", type number}, {"Tackle", Int64.Type}, {"%Tackle", type number}, {"Game", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Player ", "Game", "Pass", "%Pass", "Tackle", "%Tackle"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Player ", "Game"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Attribute", "Value"}, {"Added Index1.Attribute", "Added Index1.Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Added Index1", each Text.StartsWith([Attribute], "%")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Index.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "%Action"}, {"Added Index1.Attribute", "Count"}, {"Added Index1.Value", "Value2"}})
in
    #"Renamed Columns"

Best Regards,

Dedmon Dai

[camargos88 · Accepted solution]

Hello @Jeremy19 ,

Check this mcode:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABIGehZA0hTMMgeS7oZKsTrRSk4geYioJZA0h6uEyoP1G8F1mcFVuhsh9EPMNwUzFcBsM6iKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Player = _t, Pass = _t, #"%Pass" = _t, Tackle = _t, #"%Tackle" = _t, Game = _t]),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"Player", "Game", "Pass", "%Pass", "Tackle", "%Tackle"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Player", "Game"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.Join(
                            Table.AddIndexColumn(Table.SelectRows(#"Unpivoted Other Columns", each not Text.StartsWith([Attribute], "%")), "Index", 1,1), {"Index", "Player", "Game"},
                            Table.AddIndexColumn(Table.RenameColumns(Table.SelectRows(#"Unpivoted Other Columns", each  Text.StartsWith([Attribute], "%")), {{"Attribute", "%Action"}, {"Value", "Value2"}}), "Index", 1,1), {"Index", "Player", "Game"}
                        ),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"