Error 500 is an internal server error, where it could be a result of many factors.
To simplify, are you able to first connect the file from the query editor?
Here is how I connect in SharePoint:
- In the query editor, go to > New Source > More > Sharepoint Folder > Type or Paste in to Sharepoint path.
In the query editor, replace sharepoint.files to sharepoint.contents.
- This will give a UI that is similar to navigating within any windows folders.
- Click on Table in Row 1, Column Content, Name: Shared Documents. Your file should be within this folder. Navigate to the file as you would in a normal folder.
- Once the file is selected, the data should appear and you can proceed to do further transformation before hitting Close & Apply.