Power Query - Help combining workbooks with some shared column names but also different columns names

[TheVBKid]

Hi all, I’m not entirely sure if what I’m asking is possible and it’s fine if not (I’m at loss), I just want to exhaust all options before I say “It can’t be done”.

We have an MS Form with say 10 questions. Due to changes the form has been removed and a new one published. It has now increased to 20 questions… the results of the both the old and new MS forms have been exported into separate excel files. I’m now in a predicament where I have 2 very different looking files but I’ve been asked to provide statistics on the combined old and new data.

They have decided to split question 3 into 4 sub questions (3a, 3b, 3c and 3d) and old question 3 doesn’t quite map to any of the new 4, they have replaced question 4 with a different question and now question 4 in the old document is question 5 in the new document, question 5 is now question 6 (it’s also moved from column H to column J) and so on but then question 8 is no longer a question so question 9 and 10 are still in sync.. so everything is just mismatched. It feels as if I need to be able to map columns from the first file to other columns in the new file.

Is there a way to kind of map the columns from the old file to the new column name and at the same time future proofing it in case of further changes?

Note: Merging in Power Query didn’t return the old columns due to the sample file structure using the new file and not matching the old file. It was just a mess. Any help on this would be appreciated.

 

[johnny51981]

My eyes went all googly trying to follow what you were saying, but I think I got enough to get an idea.

What I would do is start with your old data in power query editor. Change the column header names to their new question counter parts, if they no longer in play then rename them with something noticeable for later (such as changing Question 8 to DELETE-Question 8).
Once done, Append (not merge) the new data to the renamed old data.

Something along those lines will get you through the now.

 

[TheVBKid]

My eyes went all googly trying to follow what you were saying, but I think I got enough to get an idea.

What I would do is start with your old data in power query editor. Change the column header names to their new question counter parts, if they no longer in play then rename them with something noticeable for later (such as changing Question 8 to DELETE-Question 8).
Once done, Append (not merge) the new data to the renamed old data.

Something along those lines will get you through the now.

Thanks Johnny. It was a bit googly on my eyes writing it too So I’m guessing I’d need to add blank columns into the old data query for the additional questions/columns from the new data to append to… or would it just tag any new columns onto the end? Suppose I’ll see one way or the other when l give it a try on Monday but I thought I’d ask.

 

[johnny51981]

Thanks Johnny. It was a bit googly on my eyes writing it too So I’m guessing I’d need to add blank columns into the old data query for the additional questions/columns from the new data to append to… or would it just tag any new columns onto the end? Suppose I’ll see one way or the other when l give it a try on Monday but I thought I’d ask.

No, if you change the column names on the old data before appending the new, it will automatically align where the columns are named the same, and then the columns that aren’t named the same on both will also be visible with null population.

It’s not good English, but it’s Friday.