Forum Discussion

emdnz's avatar
emdnz
Icon for Helper I rankHelper I
4 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
    4 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
    Icon for Helper I rankHelper I
    4 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
      Icon for Most Valuable Professional rankMost Valuable Professional
      4 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
        Icon for Helper I rankHelper 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 🙂