Forum Discussion
Power BI HLOOKUP Equivalent - Data Spread Across Multiple Columns
Hi there brains trust!
I am working with a dataset containing questionnaire responses, with questions and answers not being consistently captured.
Question 1 always corresponds to Answer 1, for example, but the question text differs between responses as respondees are not asked the same questions in the same order. Respondees are sometimes not required to answer all questions either. I've mocked up some example output below:
| Question 1 | Answer 1 | Question 2 | Answer 2 | Question 3 | Answer 3 | Question 4 | Answer 4 | |
| Person A | How old are you? | 22 | What is your favourite colour? | Blue | Where do you live? | UK | Do you like cars? | No |
| Person B | Do you like cars? | Yes | How old are you? | 31 | What is your favourite colour? | Green | Where do you live? | Australia |
| Person C | What is your favourite colour? | Red | How old are you? | Null | Where do you live? | USA | Do you like cars? | Yes |
The previous Excel solution was an HLOOKUP across the range of question columns to search for specific question text (e.g. "How old are you?") and then return the corresponding Answer value in the adjacent column for each respondee.
I'm hoping to apply the same in Power BI to achieve this type of output - all answers to the same question presented in the same column:
| How old are you? | |
| Person A | 22 |
| Person B | 31 |
| Person C | Null |
I need to search the entire range of Question columns (1-64) to find specific question text, and then return the answers for that question, all in the same column. I experimented using the "Conditional Column" functionality in Power Query Editor - this did what I needed it to do, but I was wondering if there's a more efficient method using DAX.
I have 64 Question columns and approximately 20 unique question variants to search and return answers for and wanted to avoid manually entering 64 clause rows per conditional column (if that's even possible!).
Any assistance or advice would be greatly appreciated!
Thank you,
Jay
9 Replies
- Greg_Deckler
Community Champion
Jay_D Try transforming your data like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDBCsIwDIZfJfS8i/MBZFNQEIYoIjJ6KDZiMSzQrhPf3lRQPKyyS1r+/0/ytW2rdugDd1CpQm34AUwWjEd4clyIVJZSTjfTgwtJ83A1gxyuR7gwyS2laor4zqF0Wk5BIDdg8o5bKauPdpc240MyGla6+O6vM6kzhnGy+WwK2dojdjm0KobeG3LmF2Q5Zewe7ThVE4myP3Go/jxS6xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Question 1" = _t, #"Answer 1" = _t, #"Question 2" = _t, #"Answer 2" = _t, #"Question 3" = _t, #"Answer 3" = _t, #"Question 4" = _t, #"Answer 4" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Question 1", type text}, {"Answer 1", type text}, {"Question 2", type text}, {"Answer 2", type text}, {"Question 3", type text}, {"Answer 3", type text}, {"Question 4", type text}, {"Answer 4", type text}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Person", "Question 1", "Answer 1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Question 1", "Question"}, {"Answer 1", "Answer"}}), #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type",{"Person", "Question 2", "Answer 2"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns1",{{"Question 2", "Question"}, {"Answer 2", "Answer"}}), #"Removed Other Columns2" = Table.SelectColumns(#"Changed Type",{"Person", "Question 3", "Answer 3"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Other Columns2",{{"Question 3", "Question"}, {"Answer 3", "Answer"}}), #"Removed Other Columns3" = Table.SelectColumns(#"Changed Type",{"Person", "Question 4", "Answer 4"}), #"Renamed Columns3" = Table.RenameColumns(#"Removed Other Columns3",{{"Question 4", "Question"}, {"Answer 4", "Answer"}}), Combined = Table.Combine({#"Renamed Columns", #"Renamed Columns1", #"Renamed Columns2", #"Renamed Columns3"}) in CombinedGreg_Deckler thank you very much, I'll give this a try!
- danextian
Super User
Hi Jay_D ,
Your raw data is not in the desired format for reporting you need to transform it first in Power Query. The three queries below will return similar results. Which one to choose depends on your actual raw data but in most cases they're pretty dynamic so you won't mostly likely have to go through transforming each question/answer.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDBCsIwDIZfJfS8i/MBZFNQEIYoIjJ6KDZiMSzQrhPf3lRQPKyyS1r+/0/ytW2rdugDd1CpQm34AUwWjEd4clyIVJZSTjfTgwtJ83A1gxyuR7gwyS2laor4zqF0Wk5BIDdg8o5bKauPdpc240MyGla6+O6vM6kzhnGy+WwK2dojdjm0KobeG3LmF2Q5Zewe7ThVE4myP3Go/jxS6xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Question 1" = _t, #"Answer 1" = _t, #"Question 2" = _t, #"Answer 2" = _t, #"Question 3" = _t, #"Answer 3" = _t, #"Question 4" = _t, #"Answer 4" = _t]), #"Replaced Value" = Table.ReplaceValue(Source, null, "placeholder",Replacer.ReplaceValue,Table.ColumnNames(Source)), Questions = let t = Table.SelectColumns(#"Replaced Value",{"Person", "Question 1", "Question 2", "Question 3", "Question 4"}), unpivot = Table.UnpivotOtherColumns(t, {"Person"}, "Question Number", "Question") in Table.AddIndexColumn(unpivot, "Index",1, 1), Answers = let t = Table.SelectColumns(#"Replaced Value",{"Person", "Answer 1", "Answer 2", "Answer 3", "Answer 4"}), unpivot = Table.UnpivotOtherColumns(t, {"Person"}, "Answer Number", "Answer") in Table.AddIndexColumn(unpivot, "Index",1, 1), Merged = Table.NestedJoin(Questions, "Index", Answers, "Index", "Answers Table" ), #"Expanded Answers Table" = Table.ExpandTableColumn(Merged, "Answers Table", {"Answer Number", "Answer"}, {"Answer Number", "Answer"}), #"Replaced Value1" = Table.ReplaceValue(#"Expanded Answers Table","Null",null,Replacer.ReplaceValue,{"Answer"}), #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Index"}) in #"Removed Columns"let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDBCsIwDIZfJfS8i/MBZFNQEIYoIjJ6KDZiMSzQrhPf3lRQPKyyS1r+/0/ytW2rdugDd1CpQm34AUwWjEd4clyIVJZSTjfTgwtJ83A1gxyuR7gwyS2laor4zqF0Wk5BIDdg8o5bKauPdpc240MyGla6+O6vM6kzhnGy+WwK2dojdjm0KobeG3LmF2Q5Zewe7ThVE4myP3Go/jxS6xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Question 1" = _t, #"Answer 1" = _t, #"Question 2" = _t, #"Answer 2" = _t, #"Question 3" = _t, #"Answer 3" = _t, #"Question 4" = _t, #"Answer 4" = _t]), #"Replaced Value" = Table.ReplaceValue(Source,null,"placeholder",Replacer.ReplaceValue,Table.ColumnNames(Source)), Questions = let columns = Table.ColumnNames(#"Replaced Value"), ColumnsToSelect = List.Combine( {{"Person"}, List.Select(columns, each Text.StartsWith(_, "Question"))}), SelectedColumns = Table.SelectColumns(#"Replaced Value", ColumnsToSelect), Unpivot = Table.UnpivotOtherColumns(SelectedColumns, {"Person"}, "Question Number", "Question") in Table.AddColumn(Unpivot, "Key", each [Person] & Text.AfterDelimiter([Question Number], "Question "), type text), Answers = let columns = Table.ColumnNames(#"Replaced Value"), ColumnsToSelect = List.Combine( {{"Person"}, List.Select(columns, each Text.StartsWith(_, "Answer "))}), SelectedColumns = Table.SelectColumns(#"Replaced Value", ColumnsToSelect), Unpivot = Table.UnpivotOtherColumns(SelectedColumns, {"Person"}, "Answer Number", "Answer ") in Table.AddColumn(Unpivot, "Key", each [Person] & Text.AfterDelimiter([Answer Number], "Answer "), type text), Merged = Table.NestedJoin(Questions, "Key", Answers, "Key", "Answers"), #"Expanded Answers" = Table.ExpandTableColumn(Merged, "Answers", {"Answer Number", "Answer "}, {"Answer Number", "Answer "}) in #"Expanded Answers"let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDBCsIwDIZfJfS8i/MBZFNQEIYoIjJ6KDZiMSzQrhPf3lRQPKyyS1r+/0/ytW2rdugDd1CpQm34AUwWjEd4clyIVJZSTjfTgwtJ83A1gxyuR7gwyS2laor4zqF0Wk5BIDdg8o5bKauPdpc240MyGla6+O6vM6kzhnGy+WwK2dojdjm0KobeG3LmF2Q5Zewe7ThVE4myP3Go/jxS6xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Question 1" = _t, #"Answer 1" = _t, #"Question 2" = _t, #"Answer 2" = _t, #"Question 3" = _t, #"Answer 3" = _t, #"Question 4" = _t, #"Answer 4" = _t]), Custom1 = List.Transform( {1..4}, (x)=> let t = Table.SelectColumns(Source, List.Combine({{"Person"}, {"Question " & Text.From(x)}, {"Answer " & Text.From(x)}})), add = Table.AddColumn(t, "No.", each x, Int64.Type) in Table.RenameColumns(add, List.Zip( { List.Combine({{"Question " & Text.From(x)}, {"Answer " & Text.From(x)}}), {"Question", "Answer"}}) )), #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Person", "Question", "Answer", "No."}, {"Person", "Question", "Answer", "No."}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Person", type text}, {"Question", type text}, {"Answer", type text}, {"No.", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"No.", "Person", "Question", "Answer"}) in #"Reordered Columns"Please see attached pbix
Hi danextian ,
Thank you very much for your work on this. I'm going to try and apply this now - I think it will give me exactly what I'm after! I'll still be able to create conditional columns based on specific questions if needed - but not have to look across all 64 question columns for the relevant data!
My actual raw data has a significant number of columns - 64 question and 64 answer columns, plus around 20-30 columns containing supplementary information.
I think the output for table 3 is the closest to what I need. Do I need to add all of my table columns to the query in order for it to work?
Thank you again,
Jay
- danextian
Hi Jay_D
The third query assumes that the actual raw data columns follow this nomenclature: Question 1 Answer 1 Question 2 Answer 2 so on and so forth. Otherwise you'll have to use the other options.
I will just create a different query for connecting to the data source and then change Source step to reference to that query. Delete everything from Source step and replace it with the external query name. Make sure that the end result of that query is similar to the original Source step and the first column in Person. You can rename it to something else later.
