Automate report from MS Form survey

The column naming is inconsistent across groups.  This needs to be corrected before re-pivoting

Code can be optimized

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxb4MwEIX/yslTIkVVgLRr1SXJVKjoUgEDxYc4CYyLTdP8+56TNILKkTJYtt+7++7ZWSZSsgj2qFGsRDpq3Q8W9NB/k0TJ0jt1CEajsrDY/STBkrUXKUHhAfgOuUDVlKpCmQsw5/5nh/rDwiJcetAX+R8+vAsfzPmRnx95+NFd/HDO3/j5LBerTEBc16bpB/d/8MrYz5Fa93OPvD7QONnVUHWqgOQt5T14mNj73miyZevO27KiluwR8nG9Dp8Av0bSHednM7q2xMpwPleflLapSUkc4CSdI920ebJ/7iz6pGb6ukv0qznpvx27KH4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Line", 1, 1, Int64.Type),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Line"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Site type","Site type (1)",Replacer.ReplaceValue,{"Attribute", "Value"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Support provided","Support provided (1)",Replacer.ReplaceValue,{"Attribute"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Add new GxP ""enhanced"" support?","Add new GxP ""enhanced"" support?(1)",Replacer.ReplaceValue,{"Attribute"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Add new GxP ""enhanced"" support?1","Add new GxP ""enhanced"" support?(2)",Replacer.ReplaceValue,{"Attribute"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Add new GxP ""enhanced"" support?2","Add new GxP ""enhanced"" support?(3)",Replacer.ReplaceValue,{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "Attribute", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Attribute", "Index"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Split Column by Delimiter",")","",Replacer.ReplaceText,{"Index"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","GxP","",Replacer.ReplaceText,{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value6", List.Distinct(#"Replaced Value6"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

You can pivot that table if you drop the AnswerKey column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJtT8IwEMe/SsNrNbC2a33pQ1RiohiMvpi8GFCkcWy1DwJJP7zHlhGQm0mz5O7+t9/9r82y3vA2xivvrZ4Gry4GMd5vRmRYztUmxre8CArKpVsrSx7Vtjc5y3pJjGPtFfFbA0XoeF4s3LKyu4DvFcGYynpibPWj52peC+/ymS6035KP0O8nKVHfQZuVKj1U07bzVa8UcabJxsjgK+oaO+GWbhefExhAzyDHkr0Qw18rZ6ov+IHNdanLT2igbcMfKoUaQ6gw30PljPZ5AQrejQPh6GUMmhQjJPVhja+0e5+C7hWYoSe1JtOgC4gEa5WnTgRHOMnB/m7ylQkOhGk3rjUkBAZqDAmJgOiRoctuAsVuSPYxHK2PHCA4duDrPbdqWQUHaZl0g9m/L1NSbALWTMCQCfjhG5G8m8uPblCmGIfXKAlLn/wC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Attribute.1", type text}, {"GxP Index", Int64.Type}, {"Value", type text}, {"Answer Key", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ID", "Attribute.1", "GxP Index", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"