Add refresh date from Microsoft Dynamics data

[Erika16]

Hello 

How can I add the refresh date into my dashboard if my data comes directly from Microsoft Dynamics? I usually go to Transform Data/ add new / and generate the date from there but in this particular dashboard the data comes direcly from Dynamics and hence I do not have that option anymore. Could anyone help? Many thanks! 

[Anonymous]

In Power Query, add a blank query and paste the following code.

let
Source = DateTime.FixedLocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Last Refresh"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Last Refresh", type datetime}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Last Refresh]), type date),
#"Insert Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([Last Refresh]), type time),
#"Added Custom" = Table.AddColumn(#"Insert Time", "Local Timezone", each DateTime.AddZone( [Last Refresh], 2), type datetimezone )
in
#"Added Custom"



//Add local timezone column in the Power BI Report to obtain the last refresh date.

Add the Last Refresh Date column and plot it in a card visual in a report. So for every refresh you will get to know the exact time the data was refreshed. (Tip: Switch on the Schedule Refresh of the Report)

[Erika16]

My problem is that in this report the data comes from Microsoft Dyanamics and hence I cannot access Power Query. Do you have any suggestions in how I could be able to add power query in this case? Many thanks indeed for your time!!! 

[Anonymous]

Can you create one more table in Microsoft Dynamics where in you can store the UTC Now time.
If yes, fetch that table too in Power BI along with all your existing tables. 
So what will happen is while your Power BI Report is refreshed then all the tables including the table storing the UTC Now time will be refreshed and plot the visual from that table. 

Thank you.
If my solution helps you, please mark it as solution.