Forum Discussion
Power Query Referencing
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.
- Apologies if I wasn't as clear as I should be, I'm looking for a PQ table that, when refreshed, adds one or more new data rows to the top of the page, and all status rows that exist in excel shift down with it.
Let me know if there's anyway I can demonstrate it more clearly.
Thanks,