Forum Discussion
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...
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"
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"
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 🙂