'How do I correct my excel pivot table from dropping a year when there are no metrics and misaligning available metrics the the incorrect year?
I have the below scorecard table that is being populated from a pivot table (See images below).
Scorecard table I'm creating from pivot table Pivot table
below are the VLOOKUPs I am using to pull the metrics from the pivot table into the scorecard table:
=IFERROR(VLOOKUP($C8,$AF$6:$AH$9,2,0),"")
=IFERROR(VLOOKUP($C8,$AF$3:$AH$9,3,0),"")
the 2 and 3 in the vlookup represent the year i need the data for.
The issue is if there are no metrics for 2021, the pivot table will only have 2022 metrics listed and then my scorecard table lists the metrics intended for 2022 in the year 2021. (example in image)
I cannot always rely on 2022 being in the same column in the pivot table. it all depends on 2021 data being available.
If there is no data for 2021, how to I get it to populate a 0 in the pivot table? Is there a way to trick excel to show 2021 in the data/pivot table with a zero when 2021 is not poulating?
Thank you!
Solution 1:[1]
You could have a set of data that contains all of the years you want then use Power Query within Excel to generate two queries - the first looks at the list of years, the second queries your raw data i.e. Pre-Pivot
Use Home, Merge queries to do a left outer join between the Years query and the RawData query. You will end up with null entries for the years where there is no data. Then perform the pivot on the output of that merged query
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | CHill60 |