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

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"

9 Replies

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