Show date last saved for excel file source?

[MojoGene]

One of the data sources in my data model is an Excel workbook. I would like to display the date that the Excel workbook was last saved. Is there an easy way to do this?

[fhill · Accepted solution]

In Query Editor select 'New Source' and 'Blank Query'.  Next click Advanced Editor and past one of these options:

If you data is local or on a network share:

let
Source = Folder.Contents("c:\temp")
in
Source

If you data is on a sharepoint or One Drive folder:   (This one is easier if you go thru the Sharepoint option under Get Data - More, so PowerBI can record your credentials.)

let
Source = SharePoint.Files("https://<Org Name>.sharepoint.com/sites/<group Name>", [ApiVersion = 15])
in
Source

Filter the results as desired to get 'Date Accessed' for the file you need.

[Anonymous]

This is great information and useful but, unfortunately, with OneDrive and SharePoint the "Date last Saved" for the file does not always match the "Date Modified" date returned (and the file "Content Created" date rarely matches the "Date Created" returned).

Is there a way to get those two fields as well?

[Note: originally was not able to post screenshots to show what I am talking about]

[gustavoalkmin]

Hey @fhill , that worked perfectly. Thanks a lot!

One additional question: is there any way or function to get from the Sharepoint file the "Modified by" field so I can add not only the las modified date and time, but also the person who did it? Thanks again!

[d1x0nl30ng]

i tried using the below in advance editor but returned below error. Any idea what went wrong or is it this method unable to go beyond Private folders in sharepoint ?

error when using below:

let
Source = SharePoint.Files("https://abcde.sharepoint.com/sites/AAA/BBB/CCC/Private/EEE", [ApiVersion = 15])
in
Source

no error when using below but my source file is few level below CCC

let
Source = SharePoint.Files("https://abcde.sharepoint.com/sites/AAA/BBB/CCC", [ApiVersion = 15])
in
Source

[d1x0nl30ng]

So instead of using sharepoint.files , i added the source as sharepoint folder, up to before /private and filter the files from there. Managed to workaround this way.

[DGA]

Hello. My Excel file is on a Web link (dropbox link). How should the sintaxis be?

Thank you!

Diego

[frankreich]

Thanks, this works great if the date-format of 'Date Accessed' and all dates e.g. "date-of-incident" in the content of that file, have the same date-format. I am really struggeling with a file, where 'Date Accessed' is in "en-us"-format (mm.dd.yyyy), but all dates in the content are in date-format "german". As PowerBI only allows to set the locale for the complete file, always one of the dates invokes an error. Unfortunatly I did not find a way to convert the date manually, even after having unchecked the automatic "type detection" in the options. A potential solution would be to be able to provide a locale for each query. In this case the first query will load the "Date Accessed" with the locale "en-us". The second query will load the content with the locale "german". Any idea on how to solve this or any other idea is highly appreciated. Thanks in advance for your support. Best regards Frank

[frankreich]

Thanks, this works great if the date-format of 'Date Accessed' and all dates e.g. "date-of-incident" in the content of that file, have the same date-format.

I am really struggeling with a file, where 'Date Accessed' is in "en-us"-format (mm.dd.yyyy), but all dates in the content are in date-format "german". As PowerBI only allows to set the locale for the complete file, always one of the dates invokes an error.

Unfortunatly I did not find a way to convert the date manually, even after having unchecked the automatic "type detection" in the options.

A potential solution would be to be able to provide a locale for each query. In this case the first query will load the "Date Accessed" with the locale "en-us". The second query will load the content with the locale "german".

Any idea on how to solve this or any other idea is highly appreciated.

Thanks in advance for your support.

Best regards

Frank

[MojoGene]

Worked perfectly! Thanks for your help.