I have about 50 excel documents, each with the following type of information:
| Area Code | Meters |
| 1010 | 50 |
| 2010 | 20 |
| 3020 | 10 |
Each excel sheet will have a date created field. The idea is to plot the meters against the design area, and show the changes over time.
Problem
The issue is, sometimes new areas are added, sometimes they are removed. But I still want these recorded. So, say the first item 1010 was only added on week 3. On week 1, it would still need to be there but as "Not available" or even 0 meters.
Approach
My thought is to import the first excel sheet in power query, set up my filtering rules and clean the data. Then duplicate that query but change the source for the next excel sheet. Rinse and repeat for all 50 or so files. I'm not sure on the approach then to visualise this without merging the data?
