Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Pivoting Columns If Duplicate Value

Hey everyone   I think this should be fairly simple, but I am running circles with this question... I know this has something to do with pivoting columns, but I really can't find a way to do so.  ...
Need Help
  • AlB's avatar
    AlB
    6 years ago

    Hi Anonymous 

    Place this M code in a blank query to see the steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k9LSy1S0lHy1zUEko4FBUX5ZYk5CmBOTlJqUYmCa2ZecUlqZp5SrA6yciNk5SCOb2Zydmqlgm9+aXEqPrXGQE5ATmlJvoJLfjqaQmN0Q50SgS4IzswtKM5Hd4AJhgMSi9JTiVEMssUjPze1CIdiU3STiVYMsiYgtQSo2L0oMy0NI9BM0cPYJ7M4kRiDsTg5FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Object Type" = _t, #"Record Name" = _t, #"Step: Name" = _t, #"Step Last Actor: Full Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Object Type", type text}, {"Record Name", type text}, {"Step: Name", type text}, {"Step Last Actor: Full Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Step: Name"]), "Step: Name", "Step Last Actor: Full Name")
in
    #"Pivoted Column"

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

AlB thank you for your reply.

 

However, it doesn't fully work. I still have duplicates... This is what I have:

 

 Approval 1Approval 2Approval 3Approval 4
O-1Albert Einstein   
O-2 Mickey Mouse  
O-2  Mickey Mouse 
O-3 Bart Simpson  
O-4 Marge Simpson  
O-4  Homer Simpson 
O-5Lisa Simpson   
O-5 Homer Simpson  
O-5  Montgomery Burns 
O-5   Peter Griffin

 

Here is my code:

 

let
    Source = Table.Combine({#"BE DE NL Approvals (processes) Q1", #"BE DE NL Approvals (processes) Q2"}),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"Step: Name"]), "Step: Name", "Step Last Actor: Full Name")
in
    #"Pivoted Column"

 

AlB's avatar
AlB
Community Champion
6 years ago

Anonymous 

Not sure what you did. If I paste my code in a blank query I get exactly your expected result. I don't have the queries #"BE DE NL Approvals (processes) Q1" or  #"BE DE NL Approvals (processes) Q2"  so I cannot see the result of the code you've provided.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Sorry, I was not precise enough.

     

    Yes, when I paste your code into blank query it works as expected - thank you.

     

    However, when I am trying to do the same with my "true" dataset, above you can see the result. The column "Step Last Actor: Full Name" is being split and moved to columns - this is OK. However, in case of your query I have only one row per each "Record Name" (which is the desired result). In the case of my "true" dataset, this is not happening.

     

    Would you happen to know why?

    • AlB's avatar
      AlB
      Community Champion
      6 years ago

      Anonymous 

      I cannot help if I don't see the actual dataset (or something that has the same structure)

      Please mark the question solved when done and consider giving kudos if posts are helpful.

      Contact me privately for support with any larger-scale BI needs, tutoring, etc.

      Cheers 

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi

        As tested, there are other columns in your data, so AlB's method won't work for you.

         

        Best Regards

        Maggie