PowerPivot lesson #3 – if the data can’t be refreshed, it should be in Excel, or copied to PowerPivot.
I added zip code data from CSV file by loading to PowerPivot, and discarded the CSV since I did not plan to update it. Bad idea. PowerPivot wants to update all data when you do Refresh All, and missing CSV file prevents it from refreshing all other tables too.
What I should have done: load data to my Excel file, then import it to PowerPivot from Excel. Or load data to temporary Excel and copy/paste it to PowerPivot. Later solution is better, since it avoids data duplication.