Renaming columns and excel

[Back2Basics]

I have a PBI semantic model that is based on the data from an excel file. This file is the output from a MS form and has been running for over a year. This year they decided they needed to change some of the questions to better understand their reporting, and I suggested they create a new form rather than mess with what they had. 

So now I have two excel files with different column names. Some of the questions, whilst worded differently, mean the same thing. I've put together another excel file that has the list of columns from each file and then a new column with a column name that an be used for reporting (there are a different number of questions in each form so the column lists are worded differently and have different numbers). I have then added a new row and used vlookup to add the new column name into each of the excel files. My hope was that I could then use this as the headers in Power Query.

I can use these are the headers but I have a couple of problems. Firstly, all the steps in my queries are based on the old column names, so nothing works. If I try to leave the 'use top row as headers' step until the end, it throws up loads of errors because the data doesn't match the data type for the column (such as the ID column has text and it's expect numbers). I've tried changing the data types but even then it throws up errors, unless I 'use top row as headers' as one of the very first steps in the query... but then I will have to rewrite all of my steps in the query.

Is that the only solution? If it is then I'll just have to do it, but it's a lot more work. 
 

[Back2Basics · Accepted solution]

I expect someone might find a better solution but this was the best we could get. 

We used an excel file to copy and transpose paste the column names for each of the two tables. Then we added a third column where we added a new name that could be used on both files. Using vlookup we matched the columns from the files to the freshly name. 

In Power Query in Power BI, we used the first row as the header and then had to rename all of the query steps in advanced editor. This wasn't too bad because we used a good structure for changes. We always suggest to suers that they create folders for data load, transformation and publish. The data load folder has all the initial connections to data. Then we reference queries and move to the transformation. Most of the time transformations are done as one long query, but sometimes we break them up into different steps (different steps here being referencing the query and adding more query steps) if this is easier to follow. All queries are removed from 'enable load' apart from the publish folder queries, which again are referenced from the transformation queries. 

[Anonymous]

Hi  @Back2Basics ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lucas_LP]

Hi,

If I understood correctly, you should be able to rename the new columns to the old names at the beggining of the query. This should let the Query run as it did previously.

Then rename them back to the new name in a new final step.

Otherwise you could see the full raw Query code (in PQuery, right-click the Query, click Advanced editor) and do a find replace of each time each column is mentioned (you can ask an AI do it quickly with your column mapping, or do it with a bit of programming outside PBI if you are familiar enough, but seems unnecesary).

Hope this helps!