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

    // Define Data Source Parameters

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

    _database = "db_MyAnalytics",

    _schema = "dbo",

    // Define 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

Output:

Note: 

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

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

Thanks

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