Last Refresh Time from Power BI service to Power BI Reports

[han_rj]

Hi All,

I have a data model which is set to full refresh every day at 6 hrs interval, There is a ask to add a tile that gives the last refresh time for the data model in the Power BI report.
May I have help know how can we extract this refresh detail from Power BI service.
Output Required:

[ray_aramburo · Accepted solution]

Hi @han_rj 

You can create if with a blank query and this code in Power Query. Once produced just dragged the Last Refresh or Last Refresh Time field to a card.

let
    Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}}),
    #"Added Custom" = Table.AddColumn(Source, "ZoneTime", each DateTime.AddZone([LastRefresh], 0)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "UTC", each -6),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"UTC", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "LastRefreshTime", each DateTimeZone.SwitchZone([ZoneTime],[UTC])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"LastRefreshTime", type datetimezone}, {"ZoneTime", type datetimezone}})
in
    #"Changed Type1"

Note: On the Add Custom1 step I subtracted -6 to adjust to my time zone. Adapt it accordingly.

[v-lgarikapat · Accepted solution]

Hi @han_rj 

Thanks for reaching out to the Microsoft fabric community forum.
@lutz_bendlin @ray_aramburo Thanks for your Prompt Response

@han_rj ,

Below is the step-by-step guide to implementing it

Step-by-Step Guide

Step 1: Open Power BI Desktop

1. Launch Power BI Desktop.
2. Go to Home tab → click on Transform data to open Power Query Editor.

 Step 2: Create a New Query

1. In Power Query Editor, click on:

• Home tab → Advanced Editor.

1. Replace any existing code with your full M code:

let

    CurrentUTC = DateTimeZone.UtcNow(),

    Year = Date.Year(CurrentUTC),

    // DST starts: Second Sunday of March at 2 AM

    DSTStart = Date.StartOfWeek(#datetimezone(Year, 3, 8, 2, 0, 0, 0, 0), Day.Sunday) + #duration(7, 0, 0, 0),

    // DST ends: First Sunday of November at 2 AM

    DSTEnd = Date.StartOfWeek(#datetimezone(Year, 11, 1, 2, 0, 0, 0, 0), Day.Sunday),

    // Choose correct offset: -4 (EDT) or -5 (EST)

    EasternOffset = if CurrentUTC >= DSTStart and CurrentUTC < DSTEnd then -4 else -5,

    // Convert UTC to EST/EDT

    EasternTimeWithZone = DateTimeZone.SwitchZone(CurrentUTC, EasternOffset),

    // Remove time zone to keep the actual Eastern time

    EasternTime = DateTimeZone.RemoveZone(EasternTimeWithZone),

    // Format to text with AM/PM

    FormattedEasternTime = DateTime.ToText(EasternTime, "dd-MM-yyyy hh:mm:ss tt"),

    // Output as table

    Last_Refresh_DateTime = Table.FromRecords({[LastRefresh = FormattedEasternTime]})

in

    Last_Refresh_DateTime

1. Click Done.
2. You will now see a new table with one row and one column (LastRefresh) showing the current Eastern time.

 Step 3: Load It Into Power BI

1. Click Close & Apply in Power Query Editor.
2. Now, this table will appear in the Fields pane in Power BI as Last_Refresh_DateTime.

Step 4: Use It in Your Report

1. Drag the LastRefresh field into a Card visual:

• Click on Card visual from the Visualizations pane.
• Drag LastRefresh into the Values field well.

1. This will display the Eastern Time Last Refresh timestamp on your report.

 Step 5: Publish to Power BI Service

1. Save your report.
2. Click Home > Publish.
3. Sign in to your Power BI account if prompted.
4. Choose the desired workspace.
5. Wait for the upload to finish.

Step 6: Set up Scheduled Refresh (Optional)

1. Go to https://app.powerbi.com.
2. Navigate to your workspace and select the report dataset.
3. Click Settings > Dataset settings > Scheduled refresh.
4. Turn on Scheduled refresh and set your desired frequency (e.g., daily/hourly).
5. Make sure a data gateway is installed and configured only if your source requires it.
   
   
   I have uploaded the PBIX files for your reference. Please let me know if you need anything else
   
   If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.
   

   We appreciate your engagement and thank you for being an active part of the community.

   Best regards,
   LakshmiNarayana.

[v-lgarikapat]

Hi @han_rj ,
Thanks for getting back to me. Could you explain a bit more about the 4-hour window you mentioned?

Best Regards,
LakshmiNarayana

[v-lgarikapat · Accepted solution]

Hi @han_rj 

Thanks for reaching out to the Microsoft fabric community forum.
@lutz_bendlin @ray_aramburo Thanks for your Prompt Response

@han_rj ,

Below is the step-by-step guide to implementing it

Step-by-Step Guide

Step 1: Open Power BI Desktop

1. Launch Power BI Desktop.
2. Go to Home tab → click on Transform data to open Power Query Editor.

 Step 2: Create a New Query

1. In Power Query Editor, click on:

• Home tab → Advanced Editor.

1. Replace any existing code with your full M code:

let

    CurrentUTC = DateTimeZone.UtcNow(),

    Year = Date.Year(CurrentUTC),

    // DST starts: Second Sunday of March at 2 AM

    DSTStart = Date.StartOfWeek(#datetimezone(Year, 3, 8, 2, 0, 0, 0, 0), Day.Sunday) + #duration(7, 0, 0, 0),

    // DST ends: First Sunday of November at 2 AM

    DSTEnd = Date.StartOfWeek(#datetimezone(Year, 11, 1, 2, 0, 0, 0, 0), Day.Sunday),

    // Choose correct offset: -4 (EDT) or -5 (EST)

    EasternOffset = if CurrentUTC >= DSTStart and CurrentUTC < DSTEnd then -4 else -5,

    // Convert UTC to EST/EDT

    EasternTimeWithZone = DateTimeZone.SwitchZone(CurrentUTC, EasternOffset),

    // Remove time zone to keep the actual Eastern time

    EasternTime = DateTimeZone.RemoveZone(EasternTimeWithZone),

    // Format to text with AM/PM

    FormattedEasternTime = DateTime.ToText(EasternTime, "dd-MM-yyyy hh:mm:ss tt"),

    // Output as table

    Last_Refresh_DateTime = Table.FromRecords({[LastRefresh = FormattedEasternTime]})

in

    Last_Refresh_DateTime

1. Click Done.
2. You will now see a new table with one row and one column (LastRefresh) showing the current Eastern time.

 Step 3: Load It Into Power BI

1. Click Close & Apply in Power Query Editor.
2. Now, this table will appear in the Fields pane in Power BI as Last_Refresh_DateTime.

Step 4: Use It in Your Report

1. Drag the LastRefresh field into a Card visual:

• Click on Card visual from the Visualizations pane.
• Drag LastRefresh into the Values field well.

1. This will display the Eastern Time Last Refresh timestamp on your report.

 Step 5: Publish to Power BI Service

1. Save your report.
2. Click Home > Publish.
3. Sign in to your Power BI account if prompted.
4. Choose the desired workspace.
5. Wait for the upload to finish.

Step 6: Set up Scheduled Refresh (Optional)

1. Go to https://app.powerbi.com.
2. Navigate to your workspace and select the report dataset.
3. Click Settings > Dataset settings > Scheduled refresh.
4. Turn on Scheduled refresh and set your desired frequency (e.g., daily/hourly).
5. Make sure a data gateway is installed and configured only if your source requires it.
   
   
   I have uploaded the PBIX files for your reference. Please let me know if you need anything else
   
   If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.
   

   We appreciate your engagement and thank you for being an active part of the community.

   Best regards,
   LakshmiNarayana.

[han_rj · Accepted solution]

How can I change it to a 4 hours window

[lutz_bendlin]

Obligatory warning: That Refresh timestamp is largely meaningless. Better find a date in your fact table that can indicate the actual freshness of the data.

[ray_aramburo · Accepted solution]

Hi @han_rj 

You can create if with a blank query and this code in Power Query. Once produced just dragged the Last Refresh or Last Refresh Time field to a card.

let
    Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}}),
    #"Added Custom" = Table.AddColumn(Source, "ZoneTime", each DateTime.AddZone([LastRefresh], 0)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "UTC", each -6),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"UTC", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "LastRefreshTime", each DateTimeZone.SwitchZone([ZoneTime],[UTC])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"LastRefreshTime", type datetimezone}, {"ZoneTime", type datetimezone}})
in
    #"Changed Type1"

Note: On the Add Custom1 step I subtracted -6 to adjust to my time zone. Adapt it accordingly.