Forum Discussion
Pivot columns that have repeated row values - other solutions don't seem to work with my data
The problem is repeated fields in your data. For instance, for sid 6 there are several repeated values for name (see below). That causes the error when pivoting. In the data you've shared, it seems like all those repetitions have the same value. For instance, below for sid 6, all rows with name = contact_registered_before have value = Yes
all rows with name = contact_registered_before_with have value = Submitted to the OFHA
If that is always the case, simply eliminating duplicates before the pivoting will solve it. Otherwise, we will first need to determine how to treat those cases. For example, if for sid 6 you had two rows with
name = contact_registered_before but one with value = Yes and one with value = No, which one will you choose. The M code for the previous example adding the step to remove the duplicates:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJMSSlKLS4GsgyNjBUcnZwVgkuAnNDi1KL4zLy0fKVYHYjCgIz8vFQgbWpqqmsKAVjVOaZDVGFIGqHYZmJqpuDi6obNNiMk24CO0gUqNbewNMBuINg2Y2NUyVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Field = _t, Value = _t, #"Page ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Field", type text}, {"Value", type text}, {"Page ID", type text}}), #"Removed Duplicates" = Table.Distinct(#"Changed Type"), #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Field]), "Field", "Value"), #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"User ID", "Address", "Phone", "Age", "Page ID"}) in #"Reordered Columns"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
sid name value 6 contact_registered_before Yes 6 contact_registered_before Yes 6 contact_registered_before Yes 6 contact_registered_before_with Submitted to the OFHA 6 contact_registered_before_with Submitted to the OFHA 6 contact_registered_before_with Submitted to the OFHA 6 contact_registered_other_groups Yes 6 contact_registered_other_groups Yes 6 contact_registered_other_groups Yes 6 country Canada 6 provide_details Fugiat voluptatem ve 6 provide_details Fugiat voluptatem ve 6 provide_details_other_groups Deserunt qui nisi no 6 provide_details_other_groups Deserunt qui nisi no 6 province_canada Northwest Territories 6 submitting_behalf Yes
Community ChampionHi Steveyd78
Place the following M code in a blank query to see the steps
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJMSSlKLS4GsgyNjBUcnZwVgkuAnNDi1KL4zLy0fKVYHYjCgIz8vFQgbWpqqmsKAVjVOaZDVGFIGqHYZmJqpuDi6obNNiMk24CO0gUqNbewNMBuINg2Y2NUyVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Field = _t, Value = _t, #"Page ID" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Field", type text}, {"Value", type text}, {"Page ID", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Field]), "Field", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"User ID", "Address", "Phone", "Age", "Page ID"})
in
#"Reordered Columns"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks, I will work that through and see how well I understand the steps. Really apprecaited!