I am creating a pivot table in excel using the data model so i can get distinct count (see pic 1) but every time i add new field to the data source and change the data source in the pivot table so it captures the new field, the pivot table will get messed up and disappear(see pic 2) and have to create the pivot again. any solution?
Turn your source data into a Table, and then use the Change Data Source one more time. You'll see that instead of saying "Sales!$A$1:$AQ$43" it will say "Table1" in the Change Data Source dialog. From that point on, you never need to use that Change Data Source option again...you can just refresh the Pivot and it will bring through any new columns added to the Table automatically.
