how to make a count of last refreshed in powerquery

last_refreshed.bas

Start with the power query below:

let
    Source = SourceData,
    //the source needs to be a table that is in powerquery
    #"Counted Rows" = Table.RowCount(Source),
    #"Converted to Table" = #table(1, {{#"Counted Rows"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Total_records"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Last_Refresh", each DateTime.LocalNow()),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each DateTime.Date([Last_Refresh])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Time", each DateTime.Time([Last_Refresh])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Last_Refresh", type datetime}, {"Date", type date}, {"Time", type time}, {"Total_records", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Last_Refresh", "Date", "Time", "Total_records"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Total_records", Int64.Type}})
in
    #"Changed Type1"


Then add the VBA below:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Sheet13"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 Dim aws As Worksheet
 Dim nws As Worksheet
 Dim tbl As ListObject
 Dim newRow As ListRow
 Dim Lr As Long

 Dim Last_Refresh_str As String
 Dim Date_str As String
 Dim Time_str As String
 Dim Total_str As String
 Dim increased_str As String
 Dim current_str As String

 Set aws = Worksheets("last_refresh")
 Set nws = Worksheets("refresh_history")
 Set tbl = nws.ListObjects("history_table")

 With aws
    Last_Refresh_str = .Range("$A$2")
    Date_str = .Range("$B$2")
    Time_str = .Range("$C$2")
    Total_str = .Range("$D$2")
 End With

 With nws
    On Error Resume Next
    Lr = .Cells.Find(What:="*", _
                            after:=.Range("A1"), _
                            LookAt:=xlPart, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    '^--need to get last row of the refresh_history sheet
    current_str = .Range("$D$" & Lr)
 End With

 increased_str = Total_str - current_str

 If increased_str < 1 Then
     '^--check if there was an increase
     With aws.QueryTables(1)
         If Not .Refreshing Then
             .Refresh
             Application.StatusBar = "Query is currently refreshing: please wait"
         End If
     End With
 End If

 increased_str = Total_str - current_str

 If increased_str < 1 Then
     '^--if still no increase
     Application.StatusBar = "Query refreshed but no new rows added."
 Else
     Set newRow = tbl.ListRows.Add
     '^--add new row for new records

     With newRow
         .Range(1) = Last_Refresh_str
         .Range(2) = Date_str
         .Range(3) = Time_str
         .Range(4) = Total_str
         .Range(5) = increased_str
     End With
     Application.StatusBar = "Query refreshed with " & increased_str & " new rows added."
 End If

End Sub

''===========================================================================================
'' Date: .............. 2022-07-13
'' Program: ........... last_refreshed.bas
'' Website: ........... weburl
'' Description: ....... use this to create a refresh history tab in excel
'' Installs to: ....... Post_Trial_Caseloads/m_code
'' Compatibility: ..... Excel
'' Contact Author: .... [email protected]
'' Copyright © ........ n/a 2022. All rights reserved.
'' Called by: ......... n/a
'' Called to: ......... n/a
'' Arguments: ......... n/a
'' Start with a query called "last_refreshed" and this will timestamp
'' each time that a query on a sheet is refreshed and place that in a tab
'' called "last_refreshed". Then create a sheet called "refresh_history"
'' and this sheet will be where this vba code will log the refresh
'' history. make sure you name the table "history_table" where you want it
'' to be logged.
''
''===========================================================================================