Challenges: Final Friday Fix: March 2019 Challenge

On the final Friday of each month, we’re going to set an Excel challenge for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There’s no prizes at this stage, you’re playing for bragging rights only!

Confusing Column Names with Power Query and Power Pivot

Welcome to this week’s Friday Fix. This week we are pulling inspiration from our consulting work, this being related to a task that we’ve been asked to do as part of our consulting business.

The problem here relates to how Excel, Power Query and Power Pivot handles 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 then export the data to Power Pivot to create measures and other calculations with the data table.

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

We import this data table into Power Query:

The next step, say we wish to export the data from Power Query into a table in Excel:

… no problems here so far.

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

‘We couldn’t get data from the Data Model. Here’s the error message we got:

Duplicate column ‘[Column Name]’ in the rowset.
An error occurred while processing table ‘[Table Name]’.
The current operation was cancelled because another operation in the transaction failed.

This leaves us in a sticky a tricky situation as our data table did not have any duplicate columns, did it? So how do we go about fixing this?

This is the challenge this week: can you find a solution that will allow you to circumvent this error in Excel when adding the table above into the data model in Power Pivot?

Sound easy? Have a go. We’ll publish what we think is the best solution in Monday’s blog. Have a great weekend!