Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
emdnz
Helper I
Helper I

Unpivot columns based on header name

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

1 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"

View solution in original post

9 REPLIES 9
Vijay_A_Verma
Super User
Super User

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 IDNameProjectQuality ratingQuality commentsSafety ratingSafety commentsCommunication ratingCommunication comments
1JohnJohnson1Lorum ipsum1Lorum ipsum1Lorum ipsum
2JoeJohnson5Lorum ipsum5Lorum ipsum5Lorum ipsum
3TimJohnson4Lorum ipsum4Lorum ipsum4Lorum ipsum
4JohnJohnson4Lorum ipsum4Lorum ipsum4Lorum ipsum
5JoeJohnson5Lorum ipsum5Lorum ipsum5Lorum ipsum
6TimJohnson2Lorum ipsum2Lorum ipsum2Lorum ipsum

 

Response IDNameProjectQuality commentsSafety commentsCommunication commentsAttributeValue
1JohnJohnsonLorum ipsumLorum ipsumLorum ipsumQuality rating1
1JohnJohnsonLorum ipsumLorum ipsumLorum ipsumSafety rating1
1JohnJohnsonLorum ipsumLorum ipsumLorum ipsumCommunication rating1
2JoeJohnsonLorum ipsumLorum ipsumLorum ipsumQuality rating5
2JoeJohnsonLorum ipsumLorum ipsumLorum ipsumSafety rating5
2JoeJohnsonLorum ipsumLorum ipsumLorum ipsumCommunication rating5
3TimJohnsonLorum ipsumLorum ipsumLorum ipsumQuality rating4
3TimJohnsonLorum ipsumLorum ipsumLorum ipsumSafety rating4
3TimJohnsonLorum ipsumLorum ipsumLorum ipsumCommunication rating4
4JohnJohnsonLorum ipsumLorum ipsumLorum ipsumQuality rating4
4JohnJohnsonLorum ipsumLorum ipsumLorum ipsumSafety rating4
4JohnJohnsonLorum ipsumLorum ipsumLorum ipsumCommunication rating4
5JoeJohnsonLorum ipsumLorum ipsumLorum ipsumQuality rating5
5JoeJohnsonLorum ipsumLorum ipsumLorum ipsumSafety rating5
5JoeJohnsonLorum ipsumLorum ipsumLorum ipsumCommunication rating5
6TimJohnsonLorum ipsumLorum ipsumLorum ipsumQuality rating2
6TimJohnsonLorum ipsumLorum ipsumLorum ipsumSafety rating2
6TimJohnsonLorum ipsumLorum ipsumLorum ipsumCommunication rating2

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). 

 

crjackson_0-1684343986928.png

Ideally, I would like it to come out to look like this:

 

crjackson_2-1684344522712.png

But Can work with this as well:

 

crjackson_1-1684344104374.png

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:

 

crjackson_1-1684360569062.png

 

 

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.

 

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 🙂 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors