Hello team,

First post of 2021 so HAPPY NEW YEAR to you all!! Hope you all had a great festive season and you are all staying safe.

For the past couple of days I have been working on a session that includes Customer Insights and I needed to agregate data from multiple sources one of them Excel, I have never been an intense user of Power Query because of my love / hate relationship with Power BI therefore the mistakes that I made could have been just newbie ones, but they puzzled me enough to write them down so I can remember them in the future and hopefully help someone.

Mistake 1: Incorrect path to my Excel in Onedrive

The link that Onedrive gives you when clicking on “Copy a Link” doesn’t work, you need the proper path.

Open your Excel, once saved on Onedrive, and go to Info -> Copy Path, use that URL but remember to remove ?web=1

Mistake 2: Change the name of the file or make sure you rename it especially if you are using special characters

The file that I was using was an export of Dynamics Marketing and the naming convention even had what we call “tildes” in Spanish which look like this “á”. I didn’t take care of the name before and when I imported it, my table in Power Query had the same name:

If I try and submit it I will get an error without any explanation in the UI just telling me to try again later.

However if we have a look at the request, we can get the full error message:

To solve it just rename the table.

Mistake 3: You renamed the table but did you rename all the columns too?

You might have guessed it, the same error happens with the names of the columns, so make sure you rename those as well because if you don’t this is the error that you will get.

Summary

Long story short, always remember that even though the product that you are using can be fully localized to your language, the backend doesn’t like any kind of special characters therefore it’s better to always do a run through and check that you have removed any of them.

Thanks,

Mario