Copy/Record Table Data to New Sheet after Power Query Refresh

[new11]

Hi guys,
I hope everyone is doing well!

Just a quick question, I have a table on Sheet 1, with data that is populated using power query SQL source. My apologies if my question is a little confusing, I wasn't really too sure how to word it.

What I'm needing help with is somehow making it so that data in the table on sheet 1 is copied into a new sheet in a table, creating a historical database.

The table that updates, on sheet 1; Column C is peoples names which starts at C5 ends at C9 (list of names my grow in future), then the data that changes is each coloum from D to M for each person. I've attached a simple example of the table

I'm not really too sure how I might go about achieving this but I was playing around with some code I found but haven't really gotten anywhere.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsMain As Worksheet, wsHistory As Worksheet
    Dim NextRow As Long
    Set wsMain = Sheets("Sheet1")
    Set wsHistory = Sheets("Sheet2")
   
    If Target.Address = "$D$5" Then
        NextRow = wsHistory.Cells(Rows.Count, 2).End(xlUp).Row + 1
        ' Copy values
        wsHistory.Range("A" & NextRow).Value = wsMain.Range("C5").Value
        wsHistory.Range("B" & NextRow).Value = wsMain.Range("D5").Value
        wsHistory.Range("c" & NextRow).Value = wsMain.Range("E5").Value
        wsHistory.Range("d" & NextRow).Value = wsMain.Range("F5").Value
        wsHistory.Range("e" & NextRow).Value = wsMain.Range("G5").Value
        wsHistory.Range("f" & NextRow).Value = wsMain.Range("H5").Value
        wsHistory.Range("g" & NextRow).Value = wsMain.Range("I5").Value
        wsHistory.Range("h" & NextRow).Value = wsMain.Range("J5").Value
        wsHistory.Range("i" & NextRow).Value = wsMain.Range("K5").Value
        wsHistory.Range("j" & NextRow).Value = wsMain.Range("L5").Value
        wsHistory.Range("k" & NextRow).Value = wsMain.Range("M5").Value
    End If
End Sub

I would greatly appreciate any advice or assistance.

Many thanks for any inputs!
Ps. sorry in advance for any delayed responses.

 

[GraH]

Hi,

I am confused . Do I understand correctly you want to keep the history of the previous Query results?

Refresh 1 = Output 1.
Refresh 2 = Keep Output 1 and add output 2.

This can be achieved in the same Query in Power Query.

1. You have made your first query and it generates the output. This is Q1.
2. Then you load the output again in Power Query (from table/range or from Sheet, depending on your version.)
3. Copy the source step of this query.
4. Go back to Q1 and use the advanced Editor: after the let statement you paste the code you copied. Name this step something like BufferEarlierResult. Wrap the pasted code inside Table.Buffer(). Remember to add a comma at the end of the copied line.
5. And the end of the query you add again a line: Table.Combine (BufferEarlierResult, PreviousStep).
6. Remember to name this step. Something like CombinedAll.
7. And finally update the text after the in statement. Replace whatever text there is with CombinedAll.

This way you first load the previous result in memory, then run the query to fetch new data and at the end you merge both again.

Hope that's clear and helpful.

 

[GraH]

Forgot to mention: dismiss your second Query all together. You only need tit to generate the M-code you copy paste in the first Query.

 

[new11]

Hi @GraH ,
Thanks for your post.
Got it in one! haha they were exactly the words I was looking for. "history of the previous Query results"

Apologies, I'm not really too au fait with power queries in general and am a little lost from step 1 really ahah. If its not too much hassle, some pictures might help.
When you say; "fetch new data and at the end you merge both again" does this mean that historical data is merged and summing with the new data?

Thanks again

 

[GraH]

Hi @new11

I'm trying to make this in the most simple form to explain the technique. I hope it makes some sense.

In it's most rudimentary form the query is something like

let
    PreviousResults = Table.Buffer(Excel.CurrentWorkbook(){[Name="Appended_Data"]}[Content]),  // this is loading the previous results in memory first
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],  //this can be any source with the new external data and then you'd have your query steps
    Combine = Table.Combine({PreviousResults, Source}) // this is combining both: appending the new data with the Previous result.  This is the last step
in
    Combine

Basically I add new data in the "old table", followed by a refresh of the result query in green. I had to resize the recording so i could upload the gif. You do not see I'm pressing the refresh in the menu. But that's what I'm doing.

 

[new11]

Hi @GraH

Gosh, that is very cleaver and was exactly what I was hoping to achieve!
Sorry for the slow reply, I had to look at a few general tutorials on Power Query. It's the first time I've used it.

Thanks again for saving my bacon.

 

[GraH]

Hi @new11, better a late than no reply at all. Thanks for coming back on it. Glad I could help.
Good to read you pick up PQ. You'll quickly discover it is like having ?‍ in Excel.