The file in the SharePoint does not require a gateway. It should not even ask you for it. But I can guess the issue is that within Power Query, it is taking the url path to the local instance of the file. Please share the screen shot of the M Code and I can help you with that.
Even though I said a gateway is not needed, it can be used to have user controlled refresh cadence. Yes I can definitely help you with the gateway installation. Though I would like to first try to make this non dependent on gateway. But if you wish to still have a gateway, I can help with that as well.
Another tip: The option to schedule a refresh is always greyed out, unless the machine has at least one instance of gateway installed. This is by design. The app.powerbi.com is able to figure out if a gateway is installed or not. As soon as you install any type of gateway, the options is available for scheduling a refresh.
You mentioned you installed Personal Mode gateway, is there any specific reason why are you not using the enterprise gateway?
On a single machine only one instance of each gateway can be installed (one personal mode and one enterprise mode). I would recommend using the enterprise mode,
The steps are as follows,
- download the installer file,
- Run this file, and it should ask you for the gateway name, and credentials,
- These credentials are the ones that you use in Power BI Service (@app.powerbi.com)
- There is a recovery key that must be entered, please do so and KEEP IT SAFE as it cannot be retrieved, even by Power BI Support(I know that because I used to work there :))
- Once it is installed, configure the data source in the service. (this is your SharePoint file) by adding a data source to this gateway.
Including the steps and the official documentation as well here.
@mohammedkhan11, Please do let me know if you need anything else.
@BrianJ and EDNA team, is there anyway, I can have screen share with someone asking the questions. Usually for DAX or M code related problems we do not need screen share but when it comes to troubleshooting, it is usually good and faster if someone can do the screen share.