Unpivot multiple columns without duplicates

Anonymous 

Here is how I would do it - just paste this into a blank query to inspect the steps.

The sequence is basically

1. Unpivot all columns except Profile Name
2. Split the new "Attribute" column into Attempt Number and Attribute (e.g. (1, Date), (1, Outcome) etc)
3. Filter out empty values
4. Re-pivot the Attribute column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLRNzDRNzIwtASy3RIzc1JTgAy/fIXgjPxyIMsCSTq4NDk5tbg4rTQHpEQfpBuKYnWilZzQDMOtGkWbMy43uKSWZSanKgSlppUWJ4LMMIC75dACgq5xATJMkYx1yslPzkY217O4uDQVyDU0QDEVSxAYGuPzFcgyVzTLiPF6LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Profile Name" = _t, #"Attempt 1 Date" = _t, #"Attempt 1 Outcome" = _t, #"Attempt 1 Sub-Status" = _t, #"Attempt 2 Date" = _t, #"Attempt 2 Outcome" = _t, #"Attempt 2 Sub-Status" = _t, #"Attempt 3 Date" = _t, #"Attempt 3 Outcome" = _t, #"Attempt 3 Sub-Status" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Profile Name"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Attempt ","",Replacer.ReplaceText,{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attempt Number", "Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Value] <> null and [Value] <> ""),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Sub-Status", type text}, {"Outcome", type text}})
in
    #"Changed Type1"

Regards,

Owen