Pivoting Columns If Duplicate Value

[Anonymous]

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

[AlB · Accepted solution]

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]

@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"

[AlB]

@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]

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]

@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]

Hi

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

Best Regards

Maggie

[Anonymous]

You are right, I cleaned up the data a bit and now solution from @AlB works perfectly. Thank you so much!