Hi,
I'm new to Power Query and might be missing the correct way to solve the issue I'm facing. I will appreciate If someone can point me in the right direction. I have data set that is split into 3 sections (see below) before I start working on the data I need to add a new column and name each section starting from the top 'Qty Change', 'Amount Change', and 'Freq Min Change'. The number of empty rows between each section in the downloaded report is always the same.
| Date | Fee_Type | Prior_Quantity | New_Quantity |
| 2023/06/26 | 54649 | 6009 | |
| 2023/06/10 | 5464 | 3686 | |
| 2023/07/01 | 546 | 5527 | |
| 2023/07/01 | 5416 | 5527 | |
| Date | Fee_Type | Prior_Amount | New_Amount |
| 2023/06/10 | 8797 | 500.00 | |
| 2023/06/10 | 464 | 500.00 | |
| 2023/06/27 | 546 | 3.00 | |
| 2023/07/03 | 546 | 450.52 | |
| Date | Fee_Type | Prior_Minimum | New_Minimum |
| 2023/06/21 | 100.00 | 0.00 | |
| 2023/06/28 | 100.00 | 0.00 | |
| 2023/06/27 | 85.00 | 0.00 |
In Power Query, these would be three separate tables and would require three separate queries if you are trying to keep this on one sheet in Excel. If you wish to not have three queries, then I would urge you to use Native Excel and just compare the two fields with simple math with the result in the next column.
Hi Farah,
You can use Power Query assuming your example is consistent in that each section's name is split by an underscore. If so, please see example attached.
Mynda
Thank you all for your input!! The transformation in Excel is what is being currently used every time the report is downloaded so, my intention was to automate the process using PQ and it appears that I was missing something that was right in front of my eyes! Thank you, Minda Treacy!
