Broken Queries after adding new data

[ExcelAtEverything]

HELP! ? ? ?
I am freaking out right now & I hope someone can help me. I was nearing completion of a daily/monthly/yearly sales tracker that I've been working on for quite some time now, being that I am only a few weeks new to Power Query & Pivot Tables. I had finished inputting all formulas finally (mostly GETPIVOTDATA, SUMIFS, INDEX/MATCH, etc), and then began doing heavier testing... making corrections as needed. My project is a sales tracker as I mentioned, which is supplied with data via:
(Downloaded Excel Docs in Desktop Folder>Power Query>Data Model>Power Pivot>My Sales Tracker)

In esssence what has happened is that last night I decided to update all of the Excel data files & then refresh my queries & tables.
* I've made it a point to add new additional data files & refresh every 2-3 nights up until now just to make certain nothing got too far away from me, and only ever had minor user-error problems like dropping a new data file into the wrong designated folder or something like that.

The new reports I added were not only brand new sales info, but were also to replace what I had before. The reason I replaced all the files is because there was no rhyme or reason to them as far as date range/start date, etc. I got rid of them all, and replaced them with a more structured reporting cadence where now each report runs from the 1st of month to the last of the month (or just MTD if the month isn't over yet. After I dropped them in last night, all hell broke loose! My tracker is now blank when it should be full and it's because 3 of my queries are returning (mostly) strange looking (to me, anyway) errors that I can't decipher.The small amount of info I can find on the web about this hasn't helped so far. The new reports "appear" to be the same, and look as if nothing further was added or removed to the tables/ranges contained in them, but I can't be certain because I don't have the old former docs anymore.(My Recycle bin was enormous and I emptied it (won't make that mistake again). But at the same time I'm glad I did otherwise I would not have know there was a potential problem with the reports being downloaded now as opposed to before.

Although I wouldn't want to post the entire document publicly, I would be more than happy to share it with someone privately if that helps (along with a copy or two of the data files if I have to. I am supper-stressed because of the amount if time I've invested in this. It can't just come to this where I'm left with a broken sheet due to an attempted refresh.
I would be so grateful to anyone who could help me here with this. There's so much I could upload that i don't know where to start, so I'll start simple. Please let me know what other info I can supply you with to better grasp things. Thaks so much:

The first 2 errors shown say this:
Expression.Error: The column 'Column1' of the table wasn't found.
Details:
Column1
But I don't know what Column1 is. I popped it into a text editor to search for Column1 and to find the correct Column1, but it's still unclear (to me) why its not found.
The other error is just gibberish.

 

[RoryA]

I suggest you open up one that is failing, start at the Source step and move down until you hit one that causes an error. That will at least narrow down where it starts to go wrong (there may be more than one step in error of course). It's also probably worth just opening the source files in Excel, saving and closing them, as sometimes programs create what seems to be a valid Excel file but whose structure isn't quite what it should be.

 

[ExcelAtEverything]

I suggest you open up one that is failing, start at the Source step and move down until you hit one that causes an error. That will at least narrow down where it starts to go wrong (there may be more than one step in error of course). It's also probably worth just opening the source files in Excel, saving and closing them, as sometimes programs create what seems to be a valid Excel file but whose structure isn't quite what it should be.

Thx RoryA. I read your suggestion as "stay calm, don't freak out, just be methodical and start at the beginning." I was definitely in FULL ON freak-out mode simply because the problem felt FAR worse than it was because of the fact that it blanked out my entire sheet, and also caused a chain-reaction of other errors. But I did just what you said. I really started zoning in on the details of each step of the query (which I hadn't done before), and what exactly the words meant. When I created this particular query, I was about 2 days into even knowing what Power Query was, and so in hindsight I didn't go about it in the best way.

I started from the source, then I learned about the "Go to error" button. Long story short, what was causing the problem was a few blank columns near the end of the query which were named column1, column2, column3...up to column6. After I added in more data, those same blank columns at the end were now labelled as column22, column23...up to column24. So as for the blank columns at the end... there were slightly different names, and less of them.

How I fixed it was to early on in the query, select all of the columns that I wanted to keep, and then right-click & "delete other columns". So now it won't matter if they change names, etc. Then I had to go back thru a few other steps and delete any references to columns which had now already been deleted.

Whew! That really shook me up for a bit. Lol! Thanks for the calming advice, RoryA!

You actually changed the entire direction of my sheet a few weeks ago by suggesting I use PQ/data model. That advice is what allowed me to even get this far, because my sheet was painstakingly slow because I had all the hundreds of thousands of rows of data physically located in the sheet at first, & was not even familiar with PQ. I was heading towards a dead end. Thanks so much. ?

 

[ExcelAtEverything]

Also I switched the solution checkmark on this thread from your response to mine, since mine is the one that actually contains the answer. I always feel guilty about doing that... like I'm cheating you out of the recognition or something. So I went back to the advice I've gotten from folks here on this site, to think in terms of someone looking for an answer having the same problem as me.