Forum Discussion
User input fields in power query
I have a power query table of employees from an employees worksheet in the current workbook.
First Name
Last Name
Address
Etc.
Which I feed into Power Query.
I want to generate other tables in the workbook using the first and last names from the above table as a starting point, but adding user input fields. I was able to do this initially by having power query generate the first two columns, and put another table next to it with the user input fields to "fake it" and combine everything in a subsequent query.
I was wondering if there was a more elegant way of creating a new Excel table (and probably a new sheet to put it on) by using the two columns from the sheet I already have, plus any additional columns I need for users to fill in. Either using standard Excel or power query, if I can stop power query from zapping the user input fields on a refresh.
Note: I don't know how to have Power Query add a column without assigning it an initial value like null. That is why my user input fields are getting zapped building the entire thing in Power Query at the start.
HelloDan_TWE,
Power Query cannot preserve manual user input in columns added to a table it loads to Excel. When the query refreshes, Power Query recreates the output table and any values typed by users are overwritten.
The recommended approach is to keep the Power Query output and the user input in separate tables and then combine them.
Load the Power Query result (for example First Name and Last Name) to an Excel table.
Create a second Excel table for user input that contains the same key columns (First Name and Last Name) plus the additional fields users will complete.
In Power Query merge the query table with the user input table using the key columns.
Expand the user input columns and load the merged result.
This allows the employee list to refresh from Power Query while preserving user-entered values in the separate input table.
Microsoft documentation
https://learn.microsoft.com/power-query/merge-queries-overview
1 Reply
HelloDan_TWE,
Power Query cannot preserve manual user input in columns added to a table it loads to Excel. When the query refreshes, Power Query recreates the output table and any values typed by users are overwritten.
The recommended approach is to keep the Power Query output and the user input in separate tables and then combine them.
Load the Power Query result (for example First Name and Last Name) to an Excel table.
Create a second Excel table for user input that contains the same key columns (First Name and Last Name) plus the additional fields users will complete.
In Power Query merge the query table with the user input table using the key columns.
Expand the user input columns and load the merged result.
This allows the employee list to refresh from Power Query while preserving user-entered values in the separate input table.
Microsoft documentation
https://learn.microsoft.com/power-query/merge-queries-overview