Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Unpivot multiple columns without duplicates

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 NameAttempt 1 DateAttempt 1 OutcomeAttempt 1 Sub-StatusAttempt 2 DateAttempt 2 OutcomeAttempt 2 Sub-StatusAttempt 3 DateAttempt 3 OutcomeAttempt 3 Sub-Status
A04/04/2019FailedNo Show08/04/2019SuccessfulN/A   
B04/04/2019SuccessfulN/A      
C04/04/2019FailedDevice Refusal08/04/2019 SuccessfulN/A   
D05/04/2019BlockedDevice Issue10/04/2019 FailedNo Show13/04/2019SuccessfulN/A
E05/04/2019SuccessfulN/A      

 

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

 

ProfileNameAttempt NumberAttempt DateOutcomeSub-Status
A104/04/2019FailedNo Show
A208/04/2019SuccessfulN/A
B104/04/2019SuccessfulN/A
C104/04/2019FailedDevice Refusal
C208/04/2019SuccessfulN/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!

Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

       

      so much to learn :)

    • Ju_Ja's avatar
      Ju_Ja
      New Member
      3 years ago

      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.