Copy data before Query

togatown

New Member
Joined
Mar 2, 2005
Messages
12
I am using Power Query to update a table on the Sheet AllData via an API. I have a separate table on a sheet named Totals with a table tblTotals which has a Total Row. Before my Query refreshes my data, I want to grab my some of the values from the tblTotals Total Row and add it to a third sheet/Table with a timestamp column to log the history. I want this new table to update not overwrite the data. Can this be done with PQ? Can it be done by editing my existing PQ? if so how? I have little to no experience wit PQ but know the mechanics.

Thanks!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi,

I don't know if this can be done by PQ, but how about a a few lines of VBA that will copy the info you need, and then update the Query?

These few lines should work fine:
Code:
Sub Copy_and_Update()    
    Dim totalRow As Integer
    Dim lRow As Integer
    
    totalRow = Sheets("Totals").Range("A1").End(xlDown).Row
    lRow = Sheets("History").Range("A1").End(xlDown).Row + 1
    
    Sheets("History").Cells(lRow, 1).Value = Format(Now, "YYYY-MM-DD HH-MM-SS") 'put's a timestamp in the cell below the last row of the "history table"
    Sheets("History").Cells(lRow, 2).Value = Sheets("Totals").Cells(totalRow, 2).Value 'puts the total value from the total sheet, in the cell next to the timestamp
    
    ActiveWorkbook.Connections("Query - AllData").Refresh
End Sub
I am guessing your tables starts in A1 in the respective sheets, and that they don't have empty fields?
Either way I guess you need to modify it for your needs.

Br.
Henrik
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top