Forum Discussion

emdnz's avatar
emdnz
Helper I
3 years ago
Solved

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...
Tutorial Requests
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    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"
emdnz's avatar
emdnz
Helper I
3 years ago

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
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
3 years ago

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"
  • emdnz's avatar
    emdnz
    Helper I
    3 years ago

    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 🙂 

  • crjackson's avatar
    crjackson
    Helper I
    3 years ago

    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's avatar
      emdnz
      Helper I
      3 years ago

      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's avatar
        crjackson
        Helper I
        3 years ago

        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"