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"
Hello! I have a similar issue. We are working on an awards program for our employees. The survey is made in MS Forms. For every nominee, it creates a new column for their ratings. See below.
This is how it comes out and adds a new set of columns for each nominee (we have over 200 nominees, so unpivoting manually will not work).
Ideally, I would like it to come out to look like this:
But Can work with this as well:
This is what I have in the advanced editor:
= let
Source = FileNames,
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"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}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Start time", "Completion time"}),
in
#"Removed Columns"
Hi, in my own query I ended up using the below line, it's relatively straightforward. It focuses mainly on the "List.Select" which returns a list of all columns in the table, then does a "text.contains". In my case I only needed columns that had the word "rating" in it, as I had ratings and comments columns.
In your case it seems like you'd need to do a double unpivot, first one containing "Please Rate" and then another one containing "Caring" OR "Integrity" OR "Discovery" OR "Safety" OR "Stewardship".
Alternatively if you wanted the 2nd outcome you could do the one unpivot on "Please rate" and then you'd have to merge all other columns, possibly using a similar logic. Merge all columns that contain "Discovery" etc.
#"Unpivot if header contains rating" = Table.Unpivot(#"Removed Columns", List.Select(Table.ColumnNames(#"Removed Columns"), each Text.Contains(_,"Rating",Comparer.OrdinalIgnoreCase)), "Attribute", "Value"),
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.