cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MojoGene Member
Member

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

Accepted Solutions
fhill Established Member
Established Member

Re: Show date last saved for excel file source?

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

View solution in original post

6 REPLIES 6
fhill Established Member
Established Member

Re: Show date last saved for excel file source?

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

View solution in original post

MojoGene Member
Member

Re: Show date last saved for excel file source?

Worked perfectly! Thanks for your help.

 

 

 

frankreich Frequent Visitor
Frequent Visitor

Re: Show date last saved for excel file source?

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

Re: Show date last saved for excel file source?

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

Re: Show date last saved for excel file source?

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

 

d1x0nl30ng Frequent Visitor
Frequent Visitor

Re: Show date last saved for excel file source?

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,117 guests
Please welcome our newest community members: