Forum Discussion
Unpivot columns based on header name
Use the below code to make it dynamic.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8iDUsX5IBZI0Ce/qDRXIbOguDSXKJFYnWglI7ApqShmmWLoJCwCMssYKBaSmYtilgmGTsIiILNMsHmSXMNMqehJMyyeNMLQSVgkNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Response ID" = _t, Name = _t, Project = _t, #"Quality rating" = _t, #"Quality comments" = _t, #"Safety rating" = _t, #"Safety comments" = _t, #"Communication rating" = _t, #"Communication comments" = _t]), #"Unpivoted Columns" = Table.Unpivot(Source, List.Select(Table.ColumnNames(Source), each Text.Contains(_,"Rating",Comparer.OrdinalIgnoreCase)), "Attribute", "Value") in #"Unpivoted Columns"
can you share an example table of what the data looks like after the first unpivot? maybe the result is different than I'd expected.
Alternatively, try the second line with just 1 selection first, so remove the "or" statement and just do "Caring". I'm not sure how it'd behave taking multiple conditions.
I'm really no expert so I can only guess, hope it helps! Otherwise try the same text logic but on merges in order to achieve the 2nd result you initially shared.
I was able to use the "Please Rate" Unpivot line and then select the ones I wanted to keep, use 'unpivot other' option, and then extract and remove the numbers that were added to the column headers by the survey. However, it doesn't seem to pick up when new columns are added.