Power BI HLOOKUP Equivalent - Data Spread Across Multiple Columns

[Jay_D]

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

[danextian]

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

[Jay_D]

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.

[Jay_D]

@danextian thank you for that. I meant to mention that my actual source is a database View. Am I still able to apply the same steps that you mention here?

[danextian]

Yup but you won't be able to use Direct Query.  I would still use an external query to connect to the database and make sure that end result looked similar to the original source step.

[danextian]

I forgot to mention that you need to replace 4 with the actual number of question or answer columns

[Jay_D]

Noted, thank you!

[Greg_Deckler]

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

[Jay_D]

@Greg_Deckler thank you very much, I'll give this a try!