cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MojoGene
Helper V
Helper V

Show date last saved for excel file source?

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?

1 ACCEPTED SOLUTION
fhill
Super User I
Super User I

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.

 

Capture.PNG



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




View solution in original post

9 REPLIES 9
fhill
Super User I
Super User I

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.

 

Capture.PNG



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




View solution in original post

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?

200824_FileProps_Consolidtion.PNG200824_OneDriveProps_Consolidtion.PNG

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

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
Frequent Visitor

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
Sourcepbi SP datasource list error.PNG

 

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.

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

Thank you!

Diego

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

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

 

Worked perfectly! Thanks for your help.

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors