You don't have any gateway installed

[Jot]

Dear all,

I want to use a virtual server (Windows 10) for managing all my organisation's PowerBI files independently of user or machine.

• On the VM I installed the On-Premises data gateway in order to refresh the data in the published reports.
• Outbound ports: TCP 443 (default), 5671, 5672, 9350 thru 9354 are opened on the VM.
• The user rights for the VM, the PowerBI service and the dataset are all set to an internal service account, except for the gateway service account (which uses the default PBIEgwService).
• The test report has been published and is available to a number of colleagues.
• At the moment we're still using Power BI Pro licences instead of the Premium package.
• I have restarted the gateway (a number of times).

The gateway runs correctly:

The datasources have been setup and runs correctly (the datasource is a PowerBI report which uses two Excel files which are available from the VM which hosts the pbix):

(I also added the folder containing the source files, and the separate source files as data sources but with no success:

)

Then when trying to configure the automatic refresh trough the Power BI Service, the following error message appears:
"You don't have any gateway installed or configured for the data sources in this dataset. Please install a new data gateway, or configure the data source for an existing data gateway":

Apparently I'm still missing a step or two; any thoughts on how to solve this anyone?

Thank you in advance,

[v-yuezhe-msft]

@Jot,

Please go to File -> Options and settings ->Data source settings in Power BI Desktop to check data sources and post a screenshot about the data sources.

In addition, do you connect to .xls Excel files and want to refresh them in Power BI Service? If that is the case, refreshing against a .xls file is not supported. As stated in this article, Power BI supports importing or connecting to workbooks created in Excel 2007 and later. Workbooks must be saved as .xlsx or .xlsm file type, please save your Excel files as .xlsx files.


Regards,
Lydia

[Jot]

Dear Lydia,

Thank you for your follow-up.

These are the data sources in use:

The service account which is used to access these files has access to the network folder where the files are located.

(I misnamed the datasources in Power BI as *.xls as they're .xlsx in reality).

I hope this helps; thanks in advance, Jot

[v-yuezhe-msft]

@Jot,

Could you please post the Excel data source information under gateway? And please review my reply in this similar thread to find the correct account which is used to connect to Excel.

Regards,
Lydia

[Jot]

Dear Lydia,

I read through your other thread and updated the source location in the desktop file, in order to match the location in the gateway:

They both use \\servername\folders...\ structure.

The Excel file is located on a shared drive. When opening the Excel file properties, it shows as Z:\.

In the gateway I cannot choose the Z:\ structure as it's not accessible from the Service.

These are the (anonymised) data files I use (reportusers and userdept are in the same xlsx file):

I use validated input in the userdept table, but don't suppose this can create the issue not finding the files in the automatic refresh.

Thank you very much!

[v-yuezhe-msft · Accepted solution]

@Jot，

After you match the file path and user account between Desktop and gateway, are you able to choose the gateway option?

Regards,
Lydia

[Jot]

@v-yuezhe-msft,

I masked the paths in the images above; in reality the file paths are the same already since reading your other post.

The account which owns the datamodel is the same I use for everything: connecting to the desktop file, logging into the Service, and accessing the gateway. This is our dedicated service account, which has access to the pbix and the xlsx source files.

The only account I did not change, is the gateway service account (still NT SERVICE\PBIEgwService).

I tried again saving the files locally on the VM, updated the pbix and the gateway. Success!

So the issue is related to the network location (which includes a $ in the main folder on the server).

When I find the time I'll try to use a different network folders to find out how to make this work.

Do you have any thoughts on what may cause issues with a network drive?

Thank you for your assistance!

[Jot]

Thanks Lydia, I'll look into the solution in the other post, that might be a clue (before sharing / asking you to look at the data files).