How to return the Last Refresh Date of SQL Tables in Power BI

How use Power Query to return the Last Refresh Date of SQL Tables in Power BI
Scenario:
We have a set of tables/views sourced from Azure SQL Synapse, each containing a column name as _modified_timestamp . Using this column, we need to generate a summary dataset in Power BI that returns the last refresh date for each table/view.

Src_Tables = {"dim_Country", "dim_Product", "dim_Segment"}

We can achieve this Scenario using the following Power Query:

let

    // Data Source Parameters:

    _server = "MyServer-synapse-server.database.windows.net",

    _database = "db_MyAnalytics",

    _schema = "dbo",

    // Source Connection:

    Source = Sql.Database(_server, _database),

    // List of tables for which we need to check last refresh Date:

    Src_Tables = {"dim_Country", "dim_Product", "dim_Segment"},

 

    // Function to run native query for each table:

    GetLastRefreshDate = (TableName as text) =>

        let

            Query = "SELECT MAX(_modified_timestamp) AS Last_Refresh_Date FROM " & _schema & "." & TableName,

            Query_Result = Value.NativeQuery(Source, Query),

            WithServer = Table.AddColumn(Query_Result, "Server_Name", each _server),

            WithDb = Table.AddColumn(WithServer, "Database_Name", each _database),

            WithSchema = Table.AddColumn(WithDb, "Schema_Name", each _schema),

            WithTable = Table.AddColumn(WithSchema, "Table_Name", each TableName),

            Result = Table.ReorderColumns(WithTable, {"Server_Name","Database_Name","Schema_Name","Table_Name","Last_Refresh_Date"})

        in

            Result,

    // Apply function to get the last refresh date for each table:

    All_Results = List.Transform(Src_Tables, each GetLastRefreshDate(_)),

    // Combine each table result into one table:

    Final_Result = Table.Combine(All_Results)

in

    Final_Result

Sample Result:

Notes: 

Use parameters to define source connection details such as Server, Database, and Schema. Avoid hard‑coding these values directly within the query definition.

We can also achieve the above Scenario using the following Power Query:

let

    // Connection Parameters:

    _server   = p_server,

    _database = p_database,

    _schema   = p_schema,

    // Data Source Connection

    _source = Sql.Database(_server, _database),

    // Tables to check for last Refresh Date:

    _src_tables = {

        "fact_Sales", 

        "fact_Inventory"

        },

    // Output Table schema definition:

    _schema_table = type table [

        Attribute_ID    = Int64.Type, 

        Entity_Name     = Text.Type,

        Entity_Type     = Text.Type,

        Attribute_Type  = Text.Type, 

        Attribute_Value = Any.Type

        ],

    // Function to get last refresh date for each Table:

    _get_last_refresh_date = ( _id as number, _table as text ) =>

        let

            _query = "SELECT MAX(_modified_timestamp) AS Last_Refresh_Date FROM " & _schema & "." & _table,

            _query_result = Value.NativeQuery(_source, _query),

            _last_date = if Table.IsEmpty(_query_result) then null else _query_result{0}[Last_Refresh_Date]

        in

            #table(_schema_table, { {_id, _table, "Table", "Data Refresh Date", _last_date} }),

    // Combine the result of each Table:

    _combined_result = Table.Combine(

        List.Transform({0..List.Count(_src_tables)-1}, each _get_last_refresh_date(_ + 1, _src_tables{_}))

    ),

    // Append the Power BI Data Model Refresh Date row:

    _final_result = Table.InsertRows(

        _combined_result,

        Table.RowCount(_combined_result),

        {

            [

                Attribute_ID    = Table.RowCount(_combined_result)+1,

                Entity_Name     = "Power BI Data Model",

                Entity_Type     = "Data Model",

                Attribute_Type  = "Data Refresh Date",

                Attribute_Value = DateTime.LocalNow()

            ]

        }

    )

in

    _final_result

Sample Result:

--------------------------------------------------------------------------------------------------------

Thanks

--------------------------------------------------------------------------------------------------------