Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Add refresh date from Microsoft Dynamics data

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! 

 

 

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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)

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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.