Auto-updating a table from anther table with Power Query

[Vonsteiner]

Hey everyone,

I hope everyone is staying safe and happy!

Here are the basics of what I am working with:

• Worksheet 1 has a table named PayRequest
  • Within this table is a list of vendors who need to be paid
  • This table contains the type of payment they want (Wire or Check) along with their banking information (if they want a wire)
  • Relevant Column Headers - There are 23 columns in the PayRequest table but these are the ones that matter for what I need to do right now
    • Vendor
    • Tax ID
    • Payment Type (Check or Wire)
    • Amount
• Worksheet 3 has a table named CheckRequests
  • This table needs to only include the vendors who have requested a check
  • Column Headers
    • Bank Account - this is a dynamic drop down menu for the user to select which account the funds will be coming out of
    • Vendor - Name of Vendor from PayRequest table
    • Transaction Date - Manually entered data for date the payment is sent
    • Ref Number - Manually entered data
    • Memo - Tax ID from PayRequest table
    • Payment Amount - Amount from PayRequest table

I want to automatically enter the data for each vendor that requests a check into the CheckRequests table from the PayRequest table. The fields I need to auto-update in the CheckRequests table are: Vendor, Memo, & Payment Amount. I would like to do this in Power Query so the table updates to as many rows as needed.

I first tried just referencing the cells in the CheckRequests table to the common cell in the PayRequest table

=PayRequest[@[Vendor]]

This works but shows all vendors whether they want a wire or check.

I would like to use Power Query like I have for another table within the workbook. However, I don't believe I can include the drop down for the Bank Account which I need. I also haven't figured out how to add a blank column within Power Query for the two manually entered columns required.

The issues I am running into:

• Entering a dynamic drop down menu in Power Query (if that is even possible which I don't think it is)
• Enter blank columns for manual entry within Power Query
• Having the CheckRequests table only bring in vendor data if the vendor selected check as their payment method

Any help would be greatly appreciated. If I need to explain this better please let me know. Thank you.

 

[alansidman]

Please upload via XL2BB your sample data so that we can try to manipulate it without trying to recreate your worksheets from your descriptions.

 

[Vonsteiner]

I have uploaded a workbook with the details. I hope this is what you were looking for.

Table Workbook for MrExcel Question.xlsx
	A
7
Payment Request

Table Workbook for MrExcel Question.xlsx
	B
7
Check Requests

I have never used XL2BB so hopefully I did it correctly.

 

[alansidman]

No data uploaded. You need to highlight the entire range. You have presented blank data sheets. Please try again.

 

[Vonsteiner]

How about this attempt

Table Workbook for MrExcel Question.xlsx
	A	B	C	D	E
1	Requestor	Tax ID	Vendor	Amount	Payment Type
2	Joe	12345	Mountain Dew	$ 5,000.00	Wire
3	Joe	67890	InstaPot	$ 10,000.00	Wire
4	Joe	15920	Spotify	$ 23,456.00	Check
5
6	This is the PayRequest table in Worksheet 1
7
Payment Request

Table Workbook for MrExcel Question.xlsx
	A	B	C	D	E	F
1	Bank Account	Vendor	Transaction Date	Ref Number	Memo	Expenses Amount
2	This will be a dynamic drop down from a named range located in a data worksheet	Vendor from PayRequest table	Manually entered date	Manually entered number	Tax ID from PayRequests table	Amount from PayRequests table
3
4
5	This is the CheckRequests table
6
7
Check Requests