• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Really ugly data - easiest way to clean with Power Query

We have been tracking expenses for conferences since 2007 in a spreadsheet - one tab for each year, and each conference on that tab listed horizontally (conference 1 data in columns A and B; conference 2 data in columns C and D., etc.). In 2012, we added two blank columns between each conference. The conference names and dates are in rows 1-3 on most tabs, and 1-4 on the last few tabs, sometimes in merged cells, sometimes not. Needless to say, it is not set up well to be able to summarize data by conference.

I played around with the 2019 data in Power Query and tried every trick I know (granted, I don't know of ton of tricks), and I gave up. Ideally, I would love to combine all data into one table with conference name, conference date, conference year, actual expense, and expense description. I've been tasked with trying to come up with a way to budget and track against the budget for the individual conferences, and currently we do not have GL accounts for conferences, only for types of expenses (though I am pushing for that!).

Anyway, does anyone have any ideas on ways to use Power Query to combine all of the tabs in the workbook we track? I've uploaded a file with a sample of the format from 2007-2011 and from 2012-2019 (and beyond). For reference, we don't always track the same number of conferences each year.

If I can get this into a table format without having to do all of it manually, I will use that as the format going forward and will be able to add to it in order to maintain some sort of budget vs. actual analysis. I am just trying to avoid having to create this entirely manually. Any little bit would help.