Unpivot part of column

[Anonymous]

Hello

I'm connecting to a SQL server via direct query and a have a table looking like this:

Name	Time	Time Gold	Time Silver	Time Bronze	Answer Gold	Answer Silver	Answer Bronze
John Doe	01-01-20	6	45	21	36	6	55
Another	01-01-20	15	1	67	4	251	67

Now I need to have the table looking like this:

Name	Time	Rank	Time	Answer
John Doe	01-01-20	Gold	6	36
John Doe	01-01-20	Silver	45	6
John Doe	01-01-20	Bronze	21	55
Another	01-01-20	Gold	15	4
Another	01-01-20	Silver	1	251
Another	01-01-20	Bronze	67	67

How do I unpivot a part of the column?

[tex628 · Accepted solution]

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lEyMjAw1AUiIwMgxwyITUxBooZAwtgMKmRqqhSrE63kmJdfkpFahK7JEKQBpN7MHKQdJG0K5cbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Time = _t, #"Time Gold" = _t, #"Time Silver" = _t, #"Time Bronze " = _t, #"Answer Gold" = _t, #"Answer Silver" = _t, #"Answer Bronze " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Time", type date}, {"Time Gold", Int64.Type}, {"Time Silver", Int64.Type}, {"Time Bronze ", Int64.Type}, {"Answer Gold", Int64.Type}, {"Answer Silver", Int64.Type}, {"Answer Bronze ", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Time"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "Rank"}, {"Time", "Time_1"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.1]), "Attribute.1", "Value", List.Sum)
in
    #"Pivoted Column"

Just paste in into a blank query and take a look at the steps!

Br,
J

[Anonymous]

Aah alright I see. Thank you!