Forum Discussion
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.
Can someone support here, please?
My dataset:
| Object Type | Record Name | Step: Name | Step Last Actor: Full Name |
| Offer | O-1 | Approval 1 | Albert Einstein |
| Offer | O-2 | Approval 2 | Mickey Mouse |
| Offer | O-2 | Approval 3 | Pluto Dog |
| Offer | O-3 | Approval 2 | Bart Simpson |
| Offer | O-4 | Approval 2 | Marge Simpson |
| Offer | O-4 | Approval 3 | Homer Simpson |
| Offer | O-5 | Approval 2 | Homer Simpson |
| Offer | O-5 | Approval 4 | Peter Griffin |
| Offer | O-5 | Approval 1 | Lisa Simpson |
| Offer | O-5 | Approval 3 | Homer Simpson |
My desired output:
| Approval 1 | Approval 2 | Approval 3 | Approval 4 | |
| O-1 | Albert Einstein | |||
| O-2 | Mickey Mouse | Mickey Mouse | ||
| O-3 | Bart Simpson | |||
| O-4 | Marge Simpson | Homer Simpson | ||
| O-5 | Lisa Simpson | Homer Simpson | Montgomery Burns | Peter Griffin |
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
7 Replies
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
AlB thank you for your reply.
However, it doesn't fully work. I still have duplicates... This is what I have:
Approval 1 Approval 2 Approval 3 Approval 4 O-1 Albert Einstein O-2 Mickey Mouse O-2 Mickey Mouse O-3 Bart Simpson O-4 Marge Simpson O-4 Homer Simpson O-5 Lisa 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"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