I recently tried to help a customer who contacted me by phone with the following words: Lars, in my pivot data appear which are not contained in my Excel data model at all. I don’t know where they come from!“ After a quick look at the data model, I realized that it wasn’t a careless mistake, but that my customer was really right: there was data in the pivot that wasn’t in the data model. The data model looked like this:
Note the yellow cells where the project manager is missing. The pivot table was as follows:
In the pivot table Mike Schuhmacher appeared as project manager, although he was not included anywhere in the data model. Even a refresh of the data model did not change the situation. The only solution was to create a new pivot table. This then showed the expected result:
But I still had no explanation for the result. Suddenly my customer said: „Maybe someone has overwritten the value in the pivot, too.“ And then I learned something about pivot tables that I’ve never seen in the last 11 years I’ve used them:
One empty value was overwritten manually , which automatically replaced all empty values by Mike Schuhmacher. This remained unchanged even after a refresh. I was aware that I could change the column caption of pivots manually, but this behavior was new to me and the solution to the problem was all the more surprising for me. Maybe this is also helpful for one or the other.
Many greetings from Germany and a Merry Christmas and a Happy New Year for you.
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…