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:

Notes:
We can use the below code to return the table with only a record for the Power BI Data Model refresh Date Time in CET.

let

    _CET_DateTime = DateTimeZone.RemoveZone(

                    DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 2)

                    ),

    _Source = #table(

        type table [

            Attribute_ID    = Int64.Type, 

            Entity_Name     = Text.Type,

            Entity_Type     = Text.Type,

            Attribute_Type  = Text.Type, 

            Attribute_Value = DateTime.Type

        ],

        {

            {1, "Power BI Data Model", "Data Model", "Data Refresh Date", _CET_DateTime }

        }

    )

in

    _Source

By default, the DateTime.LocalNow() will return the datetime of the Local Time Zone (IST for India).

We can use the below Power Query function to convert the Time Zone to CET (UTC+2 Hours):
DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 2)

The DateTimeZone.RemoveZone function will ensure to remove TimeZone value from result and keep only the DateTime value.

In DAX, the TODAY() function returns the Today Date. The NOW() function returns the UTC Date Time stamp by default.

We can use the below DAX logic to convert the UTC Datetime to CET:

CET Time Now = NOW()+TIME(2,0,0)

IST Time Now = NOW()+TIME(5,30,0)

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

Thanks

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