Hi, I currently have a process in Power Query in Excel that captures changes in the source data, which is then added to another reference table, which in turn is used to identify the changes in the source data.
In Power Query in Excel this is fairly straightforward but does involve a manual step. Table A is the reference table, which sits on a worksheet in the Excel file. Table B is the source table. My query brings back rows from Table B that meet certain criteria, then filters these against Table A so only rows that do not exist in Table A are retained.
I then manually copy and paste the output from the query to the bottom of Table A in the workbook, so that when I next refresh the query it will de-dupe against the updated Table A.
I'm now trying to replicate this in Power BI Dataflows, but I'm stuck at how to recreate the manual step, i.e. appending the new rows to Table A, so that on the next refresh the updated Table A is referenced.
To be clear, if I simply append Table A to the Query results as a new table each time, I will miss items that have changed multiple times, as I will only see the current instance and the instance in Table A. I can’t work out how to update Table A from the Query results without creating a loop that won’t execute properly.
