locked
Unable to see entire SharePoint List from Full Path Source RRS feed

  • Question

  • I am trying to get the latest Excel file in a SharePoint list.

    The URL, if pasted in IE goes straight to the SharePoint page and shows Excel files 1 to 30

    clicking on a button at the bottom of the page displays Excel files 31 to 47, this grows by 1 excel file every month, the latest file being appended at the bottom each time.

    What I need is to retrieve the Latest Excel file in the List.  This is dictated by either the filename in descending order or the Modified date in descending order.

    If I order by Modified or by Name and then copy the resultant URL created Power Query I see the List in Web View in navigator as expected but to be able to Load or Edit the list I need to select the Path or Document at which point I no longer see the list but instead a table view containing:

    ----------------------------------------------

    Kind           Name     Children     Text

    ----------------------------------------------

    Element     HTML      Table          null

    ----------------------------------------------

    kind Regards

    Martin

    Friday, December 2, 2016 2:34 PM

Answers

  • In order to list the documents of a Sharepoint document library, you should start your query with a statement such as below:

    = SharePoint.Contents("http://myshpserver/mysite")

    As a result, you will get a list of all the libraries in the site

    In the content column, select the cell corresponding to the library that you want to explore, right click and select "drill-down". You will then have a list of all the files in this library with the "Date modified" attribute. From there it should be easy to add the filtering logic to retain only the latest files:
    - Group by Name and max of Date modified
    - merge with full list (step before group by) to retrieve the content column.  

    Friday, December 2, 2016 6:18 PM