Forum Discussion
Pivoting Columns If Duplicate Value
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
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?
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
Hi
As tested, there are other columns in your data, so AlB's method won't work for you.
Best Regards
Maggie
You are right, I cleaned up the data a bit and now solution from AlB works perfectly. Thank you so much!
