I’ve got a big report that comes out of my database which I’m try cull down and extract just a few values from. The report is set up to provide one year’s worth of data on each tab. The report includes things like beginning value, new capital inflows, % ownerships, and ending value for each month of the year. I just want to extract the final ending value for each month. The tricky thing is that the report oftentimes (but not always) lists two ending values for each month – a preliminary ending value which doesn’t account for some one-off cash flows, and a final, correct, ending value which does. I went ahead and loaded it in power query and shaped it so all I have left are the ending values for each month, but I’ve got dupes because I can’t figure out how to differentiate between preliminary and final ending values for each month. The final ending value always appears later in the list (so farther to the right in the raw data, or further down the list in my transposed power query data set), so I can use that to help, but the data set skips around so I can’t map it out based on “Is Even” or “Is Odd”. I think I’m looking for a way to remove duplicates but keep the last duplicate value in the set instead of the default of keeping the first duplicate value in the set. That would make sure I keep the final ending value – but then again there’s probably an even better way which I’m not even thinking of.
Attached is my file:
Tab #1 – raw data from my system for year #1 (yellow highlights indicate which data I need to keep)
Tab #2 – raw data from my system for year #2 (yellow highlights indicate which data I need to keep)
Note – there are a few minor differences between Tab #1 & Tab #2 in terms of the header order
Tab #3 – my desired final output
Tab #4 – my attempt at shaping the data in power query (it doesn’t quite get me all the way there, but it’s very close!)
Note – All the numbers are dummy data, so if the individual line items don’t tie to the totals out then that’s why.
Any thoughts or suggestions would be greatly appreciated. Thanks in advance!
Bookmarks