How to Create an Refresh Failed message in the Text Box in Power BI Desktop

Hey Lexixl , from the screenshot you provided it seems you are using DAX function and syntax to calculate the column you want inside Power Query. Either you change to M functions / M syntax (M is the language you can use together with Power Query, or you use DAX to create a calculated column or measure.

Nevertheless, I tend to use a mixture. I use Power Query (M) to create a table that just contains a single row and a single column.

This is the M code to create this table:

let
    Source = Table.FromRecords({[RefreshDateTime = DateTime.LocalNow()]}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RefreshDateTime", type datetime}})
in
    #"Changed Type"

Just Copy/Paste the code to a blank query using the Advanced Editor inside Power Query.

Whenever the data will be refreshed successfully this table contains a datetime value that corresponds to the latest refresh. Please be aware that I name the table "RefreshDateTime"

Then you can use DAX code to create a measure similar to this:

CurrentOrNot = 
var LatestRefreshDateTime = MAX( 'RefreshDateTime'[RefreshDateTime] )
var CurrentDateTime = NOW()
var Difference = DATEDIFF( LatestRefreshDateTime , CurrentDateTime , MINUTE )
return
IF( Difference < 60 , "current" , "not current")

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom