Power BI HLOOKUP Equivalent - Data Spread Across Multiple Columns

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