Power Pivot Principles: Duplicate Column Names

Welcome back to the Power Pivot Principles blog. This week, we look at how to avoid creating errors when dealing with fields with similar names.

The problem here relates to how Excel, Power Query and Power Pivot handle a table with similar column names. The premiss here is that we can normally import data from Excel into Power Query, make a couple of data transformations and then export the data to Power Pivot to create measures, together with other calculations with the data table.

Let’s just jump right into our example, assume we have the following Table:

We start by importing this data table into Power Query:

Then, we wish to export the data from Power Query into an Excel Table:

No problems here so far.

Now let’s try to add the data into the Data Model in Power Pivot. Excel returns with the following error message:

Our data table did not have any duplicate columns, did it? So how do we go about fixing this?

The answer may surprise you. Change the column names! If you hadn’t noticed, the original table had two very similar column names, ‘Transaction Type’ and ‘Transaction type’:

The two columns were only distinguished by the capitalised ‘T’ in ‘type’. Power Pivot does not recognise the capitalisation as a distinguishing factor, therefore treated both columns as duplicates.

Hence the simple solution: change the column name:

The column ‘Transaction type’ has been renamed to ‘Internal / External’. Let’s try to load this table into our Data Model again:

Success! We are now able to work with this data table in Power Pivot.

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about >here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.