Unpivot columns based on header name

[emdnz]

Hey all, I've found some variations of this question but somehow can't seem to apply the answers to my specific case. 

Basically I'm working with a survey response data set where I have two types of data that I'm trying to split out conditionally. Very simply put I have a "rating" question followed by a "comment" question. So for example:

Q1: Safety rating

Q1C: Safety comments

So far I've manually inpivoted rating questions separately and comment questions separately. However, this is done on specific columns. I'd like to instead have a conditional unpivot. Semantically it would look like:

if [Any value in list of column headers] contains "comments" then unpivot 

That way I'd future proof it when adding new sources that may contain new questions. 

Hopefully this makes sense and it's possible! 

THanks

[Vijay_A_Verma]

Please post some sample date 

How to get your questions answered quickly -- How to provide sample data

[emdnz]

Thank you Vijay. Below is an example of what my starting data would look like. Underneath that is the result I'm trying to achieve. So far I've only been able to achieve this my manually selecting the columns I want to unpivot but I'm trying to future-proof it by making it conditional so that any columns that get added will automatically be unpivoted if they contain the word 'rating'.

Response ID	Name	Project	Quality rating	Quality comments	Safety rating	Safety comments	Communication rating	Communication comments
1	John	Johnson	1	Lorum ipsum	1	Lorum ipsum	1	Lorum ipsum
2	Joe	Johnson	5	Lorum ipsum	5	Lorum ipsum	5	Lorum ipsum
3	Tim	Johnson	4	Lorum ipsum	4	Lorum ipsum	4	Lorum ipsum
4	John	Johnson	4	Lorum ipsum	4	Lorum ipsum	4	Lorum ipsum
5	Joe	Johnson	5	Lorum ipsum	5	Lorum ipsum	5	Lorum ipsum
6	Tim	Johnson	2	Lorum ipsum	2	Lorum ipsum	2	Lorum ipsum

Response ID	Name	Project	Quality comments	Safety comments	Communication comments	Attribute	Value
1	John	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Quality rating	1
1	John	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Safety rating	1
1	John	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Communication rating	1
2	Joe	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Quality rating	5
2	Joe	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Safety rating	5
2	Joe	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Communication rating	5
3	Tim	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Quality rating	4
3	Tim	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Safety rating	4
3	Tim	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Communication rating	4
4	John	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Quality rating	4
4	John	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Safety rating	4
4	John	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Communication rating	4
5	Joe	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Quality rating	5
5	Joe	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Safety rating	5
5	Joe	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Communication rating	5
6	Tim	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Quality rating	2
6	Tim	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Safety rating	2
6	Tim	Johnson	Lorum ipsum	Lorum ipsum	Lorum ipsum	Communication rating	2

[Vijay_A_Verma · Accepted solution]

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"

[crjackson]

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"

[emdnz]

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"),

[crjackson]

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"

[emdnz]

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.

[crjackson]

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.

[emdnz]

Amazing, that did it! So the List.Select command creates a list of, in this case the columnnames and THEN applies the Text.Contains to each row? Thank you 🙂