Forum Discussion
Is it possible to make a query automatically create new rows out of specific columns?
Hello,
I am trying to create a query that automatically hides questions based on their answer.
My end goal is to have the query to pull a file from my C drive as soon as I download it and automatically format it then create a new filtered sheet that only shows questions with a No or N/A answer.
The way the file is being downloaded currently is as shown below:
| ID | ID | q1 | q1 answer | q1 explanation | q2 | q2 answer | q2 explanation | q3 | q3 answer | q3 explnation | ID | ID |
| 1 | a | xx | xx | xx | xx | xx | xx | xx | xx | xx | 1a | e |
| 2 | b | xx | xx | xx | xx | xx | xx | xx | xx | xx | 2b | f |
| 3 | c | xx | xx | xx | xx | xx | xx | xx | xx | xx | 3c | g |
| 4 | d | xx | xx | xx | xx | xx | xx | xx | xx | xx | 4d | h |
I want to reformat it so that every question gets pulled out and made into a new row with the rest of the data pulling with it as shown below:
| ID | ID | Q | Question | Answer | Explanation | ID |
| 1 | a | q1 | xx | xx | xx | e |
| 2 | b | q1 | xx | xx | xx | f |
| 3 | c | q1 | xx | xx | xx | g |
| 4 | d | q1 | xx | xx | xx | h |
| 1 | a | q2 | xx | xx | xx | e |
| 2 | b | q2 | xx | xx | xx | f |
| 3 | c | q2 | xx | xx | xx | g |
| 4 | d | q2 | xx | xx | xx | h |
| 1 | a | q3 | xx | xx | xx | e |
| 2 | b | q3 | xx | xx | xx | f |
| 3 | c | q3 | xx | xx | xx | g |
| 4 | d | q3 | xx | xx | xx | h |
I have created another sheet with 2 columns that lists all the questions (my file has 9) and corresponding q1/q2/etc as seen here"
| q1 | xx |
| q2 | xx |
| q3 | xx |
I just don’t know how to tie these together and come up with the desired formatted table.
I’m working in Excel 2016. I also don’t seem to have Power Query. I tried downloading it, but it says I already have it installed. When I try to add it through the customize ribbon page, it does not show up.
I tried looking up VBA codes to use, but nothing seems to work for me and the Query tool bar does not have a lot to work with from what I can see. Everything has been done manually thus far.
Thanks in advance for all help!
- PowerQuery is there alright, it is on the Data tab and they stupidly called it "Get & Transform".
I meant to say the tab "Power Query" isn't there. I have been using the query via the Data tab, but I can't figure out how to create the new rows through the query.
- I know, there won't be a powerquery tab in 2016, as it is integrated into the data tab.
