Forum Discussion

MojoGene's avatar
MojoGene
Post Patron
8 years ago
Solved

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?
Need Help
  • fhill's avatar
    fhill
    8 years ago

    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.

     

fhill's avatar
fhill
Resident Rockstar
8 years ago

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.

 

  • MojoGene's avatar
    MojoGene
    Post Patron
    8 years ago

    Worked perfectly! Thanks for your help.

     

     

     

  • frankreich's avatar
    frankreich
    New Member
    7 years ago

    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's avatar
    frankreich
    New Member
    7 years ago
    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's avatar
    d1x0nl30ng
    Frequent Visitor
    7 years ago

    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's avatar
      d1x0nl30ng
      Frequent Visitor
      7 years ago

      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's avatar
        DGA
        New Member
        6 years ago

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

        Thank you!

        Diego

  • gustavoalkmin's avatar
    gustavoalkmin
    New Member
    6 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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]