Refresh spreadsheet stored on SharePoint Online

I am connecting to a spreadsheet that is stored on SharePoint Online by going to: Get Data -> Other -> Web and pasting in the URL of the spreadsheet (ending .xlsx - I truncate the bits after that).

I connected using my organizational account.

I made sure the following settings apply: Query Editor -> File -> Options and Settings -> Options -> Privacy (under Current File) -> Privacy Levels: "Combine data according to your Privacy Level settings for each source"

Data Source Settings -> Edit Permisions -> Privacy Levels: None

I published the report to a workspace. In Power BI Service I was unable to refresh the data (the error message is quite ambiguous as it mentions scheduled refresh, even though I was doing a manual refresh).

I went to the Dataset's settings where under Data Source Credentials there was an error message: "Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again".

I select Edit Credentials and selected:

Authenticaiton Method: Basic (the options were Anonymous, Windows or Basic - OAuth2 was not an option).

I entered my username and password in the format of the Office 365 UPN.

Privacy Levels: None.

This still failed.

I then changed the privacy level settings in both Query Editor (then published the report again) and Power BI Service to "Organizational" - but the option for OAuth2 still didn't appear and I got the same error message.

How do I get around these problems? What do I need to change? I want to continue to connect to just my Excel file - rather than the whole SharePoint document library (via the SharePoint folder option) as there's a lot in the library - but I need the refresh to work in Power BI Service.