Customizing Power Pivot Source Data, yes that is possible. With Power Pivot it is possible to get data from external resources. But sometimes this data needs to be modified or extra data is needed. That is possible. This article describes how to add valuable information to the source data in Power Pivot.
The report with demo data is setup in the article Getting Started with Power Pivot.
This case shows Customers with their Sales Revenue, but we might want to know more. For example: in which year, month or week was this revenue made? In our source data we have the posting date of the customer ledger records. With that we are able to deduce the variables year, month and week. We will add them in separate columns.
Power Pivot – > Manage will open the Source Data.
In this case we need to add 3 columns and extract the data from the Posting date.
- Year –> function =year([Posting_date])
- Month –> function =month([Posting_date])
Right click on add column and choose “Insert Column”. 
Change column name and enter the desired formula for this column. When entered correctly the data will be added.
We can now add data filters to the report since Year, Month and Quarter are added as PivotTable Fields. In this example they are added as Slicers. We are now able to filter on the Year, Quarter and Month. As the demo data set is not completed yet you can see that there are not so many filter options. But with more data, more options are added.
Leave a Reply