Challenges: Monday Morning Mulling: March 2019 Challenge

On the final Friday of each month, we set an Excel problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

Welcome to this month’s Monday Morning Mulling. Were you able to work around the error found in our last blog?

Last week we posted an interesting problem, where Power Pivot would return with an error when we loaded a table with two very similar columns in them.

Do note that these columns were accepted by Power Query and even successfully exported into a table in Excel.

So, how do we get around this?

The answer may surprise you. Change the column names! If you had not 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 capitalization as a distinguishing factor, therefore treated both columns as duplicates.

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:

Success! We are now able to work with this data table in Power Pivot. So how did you go? Did you find a solution other than changing the column names? Let us know, we’d be keen to hear if you think you have a better way of circumventing this error. Otherwise, we’ll see you next month for our next Final Friday Fix!

The Final Friday Fix will return on Friday 26 April with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.