I know this is a relatively old thread, but I came up with a solution to this which I thought I'd share in case it helps someone. The key is to split the process into a couple of parts, which I've broken out into four steps.
Step 1 - Import the list data. This includes the column AttachmentFiles, which you expand to get a number of new columns, the useful one being ServerRelativeUrl. Remove any columns you don't need. Grab the content of the first row in this column.
Step 2 - Create a new Web query. For the URL, combine the ServerRelativeUrl with the sharepoint site address to get something like "https://mydomain.sharepoint.com/sites/mysite/mylist/Attachments/1/myfile.xlsx". This should grab the Excel data, which you can then manipulate as necessary to achieve the formatted data you need.
Step 3 - Convert the query to a function. You do this by opening the query in advanced mode and adding the parameter line to the beginning. Also, modify the url to accept the parameter, like this:
(fileName as text) =>
let
Source = Excel.Workbook(Web.Contents("https://mydomain.sharepoint.com/" & fileName), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
...
Data manipulation steps
...
in
#"Name of final step"
Save the function with a sensible name, e.g. fnGetExcelData
Step 4 - Go back to the list query and add an Invoke Custom Function column. Pass the ServerRelativeUrl column in as the fileName parameter to the function you created. Expand the resulting column.
And that's it! You now have the Excel data from the attachment, along with any other list columns from the list. Although this will work only if all list attachments are the same Excel format, you could in theory have different types of file simply by making multiple calls to the list, and using a different function for each file type. As long as you had a list column that specified what type of Excel file it was, you could import lots of different types of Excel data from the one list.
I hope this helps someone.
Duncan
