Is it possible to make a query automatically create new rows out of specific columns?

Copper Contributor

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:

IDIDq1q1 answerq1 explanationq2q2 answerq2 explanationq3q3 answerq3 explnationIDID
1axxxxxxxxxxxxxxxxxx1ae
2bxxxxxxxxxxxxxxxxxx2bf
3cxxxxxxxxxxxxxxxxxx3cg
4dxxxxxxxxxxxxxxxxxx4dh


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:

IDIDQQuestionAnswerExplanationID
1aq1xxxxxxe
2bq1xxxxxxf
3cq1xxxxxxg
4dq1xxxxxxh
1aq2xxxxxxe
2bq2xxxxxxf
3cq2xxxxxxg
4dq2xxxxxxh
1aq3xxxxxxe
2bq3xxxxxxf
3cq3xxxxxxg
4dq3xxxxxxh


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"

q1xx
q2xx
q3xx


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!

3 Replies
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.