Excel Power Query Column not getting segregated

Question

I am compiling data from various workbooks to one using Power Query. Basically, there are 2 questions, and answer to those, all (question and the answer) in the same column. I need to pull this for 50 workbooks and put them in one column for the corresponding file name. While using power query, I can filter out the questions, but I wouldn't know which one is the response for which question (1 or 2).

And transpose is not making it any easier too. Any help please.

Answer 1

Your question isn't all that clear without some sample info, but I'll take a stab at what I think you want.

Put all your workbooks in the same folder. Then, in the ribbon at the top of the screen, select "New Source," then "File", then "Folder."

Then click the "Browse..." button and find and select the folder with the workbooks in it, and click "OK":

Then click "Combine & Edit:"

Then select the applicable sheet (on the left side of this box) and click "OK":

The worksheets' info will be appended into one table, with each row's asssociated worksheet's name in a "Source.Name" column. Like this:

(My original 4 worksheets only had the one column depicted above as "Column1" in each of them.)

I think you may be able to take it from there pretty easily.

If you want to separate the Answers from the Questions, into a separate column, you can use the "Split Column" button at the top of the Query Editor, with By Delimiter selected. (Change the default delimiter from "Comma" to whatever you want to use to separate the column. In my example above, I would use "? " as the delimiter.)