How do you use Power Query to aggregate and clean your data, then add new columns to the table that are updated outside PQ, without having those columns/fields and their data wiped out on a refresh.
In the mockup table below, the first four columns are the data generated/cleaned from PQ, and the status and completion date columns are unknown when the record is added to the incoming data, so added outside PQ as the information becomes known. The idea is that the list of items is compiled and then the list distributed to various users to update the status (sadly, manually) and given back for compiliation. As that is happening, more records are expected thru PQ, requiring a refresh of the queries involved.
My actual solution has many more columns and extracts data from multiple sources and formats to get it together. There is a substantial amount of cleaning required as our antiquated system duplicates data entry on fields, often with mismatched data, etc., so its a chore getting it to a singular output that can be distributed for updating. But that's done - what I can't get my head around is how I can distribute it and get it back to incorporate the status information.
I'm sorry if this has been answered elsewhere, as I feel its a general/basic use issue, but I'll be darned if I can find the answer clearly explained somewhere. I'm basically looking for general theory on how this can be handled, not any specifics, and as I take that to develop a solution, I expect to have more specific questions related to PQ. Thanks for any assistance that you can provide.
In the mockup table below, the first four columns are the data generated/cleaned from PQ, and the status and completion date columns are unknown when the record is added to the incoming data, so added outside PQ as the information becomes known. The idea is that the list of items is compiled and then the list distributed to various users to update the status (sadly, manually) and given back for compiliation. As that is happening, more records are expected thru PQ, requiring a refresh of the queries involved.
Job | Type | Description | Vendor | Status | CompletionDate |
1stn1003 | Rough | balloon | H.I. | Pending | |
1brk4007 | Final | round | Edwina | Started | |
1shd1001 | Rough | circular | Gale | Complete | 15-Jan |
1amt1748 | Final | funny | Evelle | Unknown | |
My actual solution has many more columns and extracts data from multiple sources and formats to get it together. There is a substantial amount of cleaning required as our antiquated system duplicates data entry on fields, often with mismatched data, etc., so its a chore getting it to a singular output that can be distributed for updating. But that's done - what I can't get my head around is how I can distribute it and get it back to incorporate the status information.
I'm sorry if this has been answered elsewhere, as I feel its a general/basic use issue, but I'll be darned if I can find the answer clearly explained somewhere. I'm basically looking for general theory on how this can be handled, not any specifics, and as I take that to develop a solution, I expect to have more specific questions related to PQ. Thanks for any assistance that you can provide.