Not able to use enterprise data gateway

[KFP]

The enterprise data gateway option grayed out after I added a directly pasted table to the report. And when I deleted the table, enterprise data gateway worked fine again. Anyone experienced this? 

Thanks in advanced! 

[v-yuezhe-msft]

Hi @KFP,

Could you please describe more details about your scenario? We need to know what is your data source and how you add pasted table to the report.

Based on my test, if we copy a table from same report and paste it to different page in the report, the enterprise gateway works without issues. However, if you add other data source into the dataset, and copy  the table of the new data source to your current report, you would need to add the new data source under enterprise gateway, otherwise the enterprise gateway option will be grayed out.

Thanks,
Lydia Zhang

[Seth_C_Bauer]

@KFP So, just to see if I can reproduce this, I want to clarify.

You were editing a report in the Service, you copied a table from the same report page, or a different report page and pasted it into the same report. This action disabled the On Premises Data Gateway after you saved the report?

[KFP]

@Seth_C_Bauer @v-yuezhe-msft, thanks for replying! 

The report used three different SQL databases as source. It was published online and used an On Premise Data Gateway for scheduled refresh. I recently copied a 5x30 Excel table and pasted directly into the report. I built some measures and visuals based on the new table and published it again. Then, the On Premise Data Gateway was disabled. I deleted the new table and republished it, then On Premise Data Gateway worked again. 

[v-yuezhe-msft]

Hi @KFP,

Yes. As Eno1978’s post, if you connect to Excel file and create new table, you would need to add the Excel data source under gateway. If you copy data from Excel and copy it to current Power BI Desktop file using “Enter data” option as shown in the following screenshot, gateway works without issues after you re-republish the  PBIX file.

Thanks,
Lydia Zhang

[KFP]

I used the "Enter Data" option and the gateway stopped working. 

[v-yuezhe-msft]

Hi @KFP,

I am not able to reproduce your issue. Could you please post the screenshot of your data sources in Power BI Desktop? You can go to File -> Options and settings ->Data source settings to check data sources. Also please help to post screenshot of data sources under your gateway.

Thanks,
Lydia Zhang

[KFP]

Three data sources:

The sources in gateway are hightlighted: 

On premise data gateway is not available:

[Seth_C_Bauer]

@KFP Looks like there are 2 on-premises SQL connections and 1 connection to an Azure SQL DB, then adding the "enter data" table disables the gateway - correct?

[KFP]

@Seth_C_Bauer Yes. 

[Seth_C_Bauer]

@KFP I see in your screenshot that the personal is offline...

And re-looking at your post, "Enterprise Gateway" is the old term for what is now called the "On Premises Data Gateway". Are you on the latest gateway download? If not, I'd recommend getting the latest to see if it resolves the issue.

I just tested this. Started with single connection to SQL On-premises / publisedh, On Premises Data Gateway (OPD) worked

added Azure SQL DB connection to report/published - OPD worked

added "enter date" / published - OPD still worked.

Try getting the latest...

(Not that it should matter, but I am using the new browser experiance in the Service as well)

[KFP · Accepted solution]

I am on the latest gateway and it still doesn't work. 

Anyway, I found a workaround by using a Google Sheet as a data source. The report works fine now. 

Thanks @Seth_C_Bauer and @v-yuezhe-msft! 

[Seth_C_Bauer]

@KFP The OPD Gateway has issues with mixing data sources, but that relates specifically to online vs. on-premises. If by "copied" you mean connected to the excel file, then you need to create a data source in the OPD Gateway to the Excel file. If you created the table within the Desktop and inserted the data you copied from Excel... I would assume you wouldn't have an issue... but maybe @v-yuezhe-msft can clarify that piece.