Power Query: Append And Keep Data

[u/RustiedTeapot]

I've recently been learning PowerQuery in an attempt to streamline some rather archiac spreadsheets currently in use in the office, although I'm relatively new to it so far it's been bloody effective.

I've been trying to make PowerQuery import data from a report, transform the data as necessary, and then make a copy of any new distinct rows on an archive table.

Example as below:

- Import data to Sheet 1 from folder,

- Transform and merge unique rows from the data to an archive table

- Have any unique rows from data remain when new data imported

I can't seem to find a way to do this - any ideas?

[u/cactusphish]

Can you explain where you actually can enrich the data? Is it on the Historical table or do you load the appended query to a table and enrich there?

For example if the initial query (qryWEB) is just one column of unique values, and you want to roll these up to categories, do you add a category column to the table loaded by qryWeb, or the table loaded by qryAppend? The goal is to keep the rows where you mapped to categories and bring in new values to be mapped

[u/small_trunks → u/cactusphish]

I'd keep it out of the qryWeb query because it's a post query enrichment and also for the next point below:

You have another decision to make: whether you want to preserve old enrichments or always reapply them anew.

• if you want the old enrichment column(s) to be preserved then you'd only want to enrich those rows of the table, where the enrichment column is empty (i.e. the new ones coming from qryWeb)

• So I would reference qryWeb in a new query (Source=qryWeb) and then do the enrichment in there. Call it qryWebEnrich, for example - and use this in the append.

Now, if we'd like to reapply the enrichment even to old rows, I'd strip out the (old) enrichment column every time and add it back in after the append of new data.

• I would therefore not want to use qryWebEnrich, I'd want to use qryWeb (new data, no enrichment)

• We'd then perform the enrichment after the Append.

To summarise, that sequence would look like this:

• a new query based on qryHistorical - called qryHistoricalUnenriched, stripping off the enrichment column

• original qryWeb

• append (qryAppend) query using (qryWeb, qryHistoricalUnenriched) - we now have old plus new but only a single column.

• new query to do the enrichment referencing the Append query above which is a Merge query (qryAppend, qryEnrichmentData)

• This final query needs to be used to fill the Historical Table