Any know how to auto insert a row in a table based off of set criteria? I have two tables I'm working with.
1st Table is the main output table with part number, price, dates, po #, vendor name.
2nd Table is a list of part numbers and licensing fees per unit per part number.
Right now everything is done manually via IFs and VLookups in excel.
In the current state, the row in red is manually inserted if the IF and Vlookup formula in the "Licensing Fee" column is "Yes".
The main driver is Part Number. All other info in the RED row was copied from the row above it except for the Price/Unit.
I want to be able to replicate and automate this manual process in Power Query. Example below.
Example
<tbody>
</tbody>
1st Table is the main output table with part number, price, dates, po #, vendor name.
2nd Table is a list of part numbers and licensing fees per unit per part number.
Right now everything is done manually via IFs and VLookups in excel.
In the current state, the row in red is manually inserted if the IF and Vlookup formula in the "Licensing Fee" column is "Yes".
The main driver is Part Number. All other info in the RED row was copied from the row above it except for the Price/Unit.
I want to be able to replicate and automate this manual process in Power Query. Example below.
Example
Date | Vendor | Part Number | PO # | Qty | Price / Unit | Total | Licensing Fee |
1/10/99 | Wylie | ZYX-1 | 12344 | 100 | 100 | $10,000 | No |
1/11/99 | ACME | XYZ-1 | 12345 | 100 | 125 | $12,500 | Yes |
1/11/99 | ACME | XYZ-1 | 12345 | 25 | $(2,500) |
<tbody>
</tbody>