cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 206 members 1,859 guests
Please welcome our newest community members: