Forum Discussion
Power BI HLOOKUP Equivalent - Data Spread Across Multiple Columns
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
Super UserHi 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.
- danextian
I forgot to mention that you need to replace 4 with the actual number of question or answer columns
Noted, thank you!
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.