How to tidy up exported data.

Copper Contributor

I've exported delivery data from a confluence table and want to have it tidied up. I was hoping to use Power Query, but as a newbie I'm struggling and I've taken far too long on attempting to figure this out without success. I'm hoping to be able to automate it so that when I get the data from a folder I've downloaded it to, it will neaten it up and put it in a little table so I can start generating cost reports.

Gem_M_0-1660322796907.png

I've anonymised the data and taken a screenshot so you can see how it shows on excel. You'll see that there are several merged and non-merged cells due to the way the addresses have been inputted.

There's about a years' worth of data on this table spanning a good few hundred rows.

How do I clean this up so they all have a single row each and each address is merged into a single cell? 

Is Power Query the best way or is there a better alternative?

4 Replies

@Gem_M Could you share a link to the anonymized file via OneDrive or similar? That would make it much easier to come up with a PQ solution.

@Gem_M 

Power Query shall work if only having hyperlinks for Key and Summary is not critical. PQ won't return hyperlinks, only texts.

I'll attempt to create it again as I deleted the data once screenshotted. Thanks for your help
No the hyperlinks aren't necessary, that's just how it exported