Unpivot multiple columns without duplicates

[Anonymous]

Hi,

This might be an incredibly easy problem to solve but for some reason I am unable to do it.

I have a table with the following column headers:

Profile Name	Attempt 1 Date	Attempt 1 Outcome	Attempt 1 Sub-Status	Attempt 2 Date	Attempt 2 Outcome	Attempt 2 Sub-Status	Attempt 3 Date	Attempt 3 Outcome	Attempt 3 Sub-Status
A	04/04/2019	Failed	No Show	08/04/2019	Successful	N/A
B	04/04/2019	Successful	N/A
C	04/04/2019	Failed	Device Refusal	08/04/2019	Successful	N/A
D	05/04/2019	Blocked	Device Issue	10/04/2019	Failed	No Show	13/04/2019	Successful	N/A
E	05/04/2019	Successful	N/A

I have been trying to unpivot the columns so that I can get the following:

ProfileName	Attempt Number	Attempt Date	Outcome	Sub-Status
A	1	04/04/2019	Failed	No Show
A	2	08/04/2019	Successful	N/A
B	1	04/04/2019	Successful	N/A
C	1	04/04/2019	Failed	Device Refusal
C	2	08/04/2019	Successful	N/A

etc.

How can I do this without ending up with multiple duplicates?

I tried to add an index column in the Query Editor before unpivoting the columns then removed the duplicate index numbers but no matter what order I did the steps in, I always ended up losing data relating to attempt 2 and 3. Pls help me solve this.

Thanks in advance!

[OwenAuger · Accepted solution]

@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

[Ju_Ja]

Hi, 
I am takeing chanse to replay on this one, and want to ask does thid line work? When I go line by line I see that there is no "null" or "empty" in previous step, but I do get "null" in result table, ex. B Attempt 2 08/04/2019.

 #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Value] <> null and [Value] <> ""),

I did search on issue about dupicates and run into this one. It did help a bit even my problem is still there. 

[Anonymous]

@OwenAuger  thanks so much, worked like a charm! life saver, can't believe I couldn't figure it out myself

so much to learn 🙂