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"
So the first line worked but i can't seem to get the second line to cooperate. I get this error message:
let
Source = FileNames,
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"ID", Int64.Type}, {"Start time", type datetime}, {"Completion time", type datetime}, {"Email", type text}, {"Name", type text}, {"Please Rate", type text}, {"Caring", Int64.Type}, {"Integrity", Int64.Type}, {"Discovery", Int64.Type}, {"Safety", Int64.Type}, {"Stewardship", Int64.Type}, {"Please Rate2", type text}, {"Caring2", Int64.Type}, {"Integrity2", Int64.Type}, {"Discovery2", Int64.Type}, {"Safety2", Int64.Type}, {"Stewardship2", Int64.Type}, {"Please Rate3", type text}, {"Caring3", Int64.Type}, {"Integrity3", Int64.Type}, {"Discovery3", Int64.Type}, {"Safety3", Int64.Type}, {"Stewardship3", Int64.Type}}),
#"Unpivot if header contains Please Rate" = Table.Unpivot(#"Changed Type", List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_,"Please Rate",Comparer.OrdinalIgnoreCase)), "Attribute", "Value"),
#"Unpivot if header contains Category" = Table.Unpivot(#"Unpivot if header contains Please Rate", List.Select(Table.ColumnNames(#"Unpivot if header contains Please Rate"), each Text.Contains(_,"Caring" or "Integrity" or "Discovery" or "Safety" or "Stewardship",Comparer.OrdinalIgnoreCase)), "Category", "Rating")
in
#"Unpivot if header contains Category"
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.