Forum Discussion
Power Query Referencing
Thanks in advance to anyone who has the chance to lend a hand with this.
At our company, we're looking to use a UI to generate job information as excel files, and then use power query in a overview file that collects all these jobs files as rows in a single table.
Our issue is we want to be able to add "check-boxes" or some sort of status columns, to update the status of these jobs after the excel files have been created using the UI, and then use formulas based on these statuses to generate emails.
Unfortunately, every time we refresh the power query table to update it with new jobs that come in, it severs the link the job row has in the actual worksheet (what cell/row/column it's assigned to) with the row it was, and therefore the formulas aren't reference the same job anymore.
Is there a work around for this that ideally doesn't use a macro? Such as a table that uses a formula to pull new values only from the power query table, and "insert" them into the static table? Or something in power query itself that allows any of these status changes to link back to the original job file?
Any advice would be greatly appreciated, thanks!
Secure_Orbit_Brad Search the web for "self referencing table power query" and you'll find several sites that explain (in detail) ways of doing this. The trick is that you connect to the initial data first (Source), transform it and then load it back to Excel. Then add a second source to the query pointing at the PQ generated table. Now, within the query, merge the original source with the 2nd table.
Perhaps difficult to grasp at first, but with help of the web search you should be able to work it out. If not, come back here with an example (share a file) of your own data and the manually inserted "check marks".
Thanks for the quick response. I can't seem to find where to add a file, but essentially what I want to do is create a table from the power query table, that cross references it so that only new rows are added. Hopefully this would prevent links between check-boxes and functions from breaking when the referenced data increases. Not sure if the attached image helps;
Snapshot of table linked directly to Power Query.
Customer Name, Email, and Phone Numbers get pulled in from a file directory using PQ directly. The Inlocates and Scheduling columns are check-boxes that change the text of Column3 (currently populated with "999"), which combines the information from the check-boxes and the customer information into an email body to be sent to the customer.
We just need the checkboxes/textfield/cell data (whatever it needs to be) that indicate the status of the job to carry with that job as new items are added to the file directory power query points to, and the rows are "shifted down"
Would it be best to accomplish this using a secondary table that looks for new values in the PQ table and then pulls them in, similar to inserting? or would a self-referencing PQ table that holds on to the statuses of the checkboxes be best? (I believe this is the method mentioned above? still not sure on how it works, but I can research more if I can get confirmation it would be the best approach).
Thanks again for your help with this.
Brad
Secure_Orbit_Brad To begin with, don't use the checkboxes. I don't think PQ can handle them. Just enter a 1 for yes and a 0 for no or any other reference. But not an object like a check box. Perhaps visually attractive but difficult to work with.
If you cant upload a file, share a link to the it via Ondrive, Dropbox or similar. Or send it to me via a private message. Hover over my avatar and press Message. Then you can add a file.
