Forum Discussion

Kieran-q_20's avatar
Kieran-q_20
Helper I
2 years ago

Ingestion Date for Power BI

Hey Everyone,

 

Does anyone know how to add a date into a Power bi report that displays the last time the EXCEL data file was updated, I want to call this the ingestion date. I know how to add in a basic refresh date, but was wondering is it possible to add a date that truly reflects the last refresh. This is because the report im working on has different refresh dates for different excel files so i wanted to reflect that accurately.

 

I'd really appreciate any and all help, thank you 

Need Help

1 Reply

  • jennratten's avatar
    jennratten
    Super User
    2 years ago

    Hello Kieran-q_20 - 

    You could use the modified date/time for each Excel file.  That can be obtained from the file properties if you use the Get Files from Folder connector as shown in the example below.  The file could have been updated with some other change besides a query refresh so this isn't completely accurate, but it may be close enough to meet your needs given complexities associated with alternative solutions.  

    If you want to go for more accuracy I recommend you add the Refreshed Date/Time as a query table for each Excel file you are needing to track.  Then you will be able to the get the respective value for each individually.  You can do this in PowerBI also.  I commonly include it in most reports.  

    This will work if you just need to get the local time:

    let
    Source = #table(
            type table[LastRefreshed=datetime],
            { { DateTime.FixedLocalNow() } } 
        )
in
    Source

    If you need to display the date/time as a specific time zone, this will work and is set at Central Time.

    let
    Source = Table.TransformColumnTypes(
        #table(
            type table[LastRefreshed_CST=text],
            { {DateTimeZone.ToText ( DateTimeZone.FixedUtcNow() - #duration ( 0,5,0,0 ), "M/d/yyyy, h:mm tt" )} } 
        ),{{"LastRefreshed_CST", type datetime}}
    )
in
    Source