Azure Table source with On Premises Gateway

[MatWebb]

Hi All, hoping someone here has had this problem too and can shed some light as to a solution:

I have a PBIX file that has 10 file or folder data sources - when uploaded to the online service and the on prem gateway installed and configured this works great. no troubles.

If i add to this same PBIX file an AzureTables data source (I.e "Source = AzureStorage.Tables("tableName"),") it all starts to go wrong.

- When i go to the online serve the and try to schedule the data refresh i no longer get an option to use the on prem gateway. It's greyed out.

- So assuming this is becuase i need to add ALL sources through the on prem gateway i go to the 'Manage gateways' link and try to add my Azure Tables source i there - but alas there doent seem to be an option for it? (see image below)

- lastly, the 'data source credentials' option is also greyed out on the page where you 'schedule refresh' which would normally allow you to add credentials for each source individually (well it would with any 2 data sources that are not using the on prem gateway.)

SO - does anyone have a way to get the above listed on prem data source options to work with Azure Tables - or perhaps some power query i should be incuding as part of my Azure Table query? Or even perhaps a setting or 2 in azure tables?

It seems really odd to me that it Azure tables doenst work with on Prem gateway so i feel like i must be doing something wrong?

Would REALLY appreciate some nudges in the right direction? Will give out kudos like toffee!

Cheers

Mat

[v-micsh-msft · Accepted solution]

@MatWebb,

Currently Online data sources and Data sources On Premises Gateway can’t work together, the workaround is to take use of Personal gateway to combine on-premises with online data sources.

Please take a moment to check and vote the Idea below:

Combine enterprise gateway and online data sources

Quoted the response from Dimah Zaidalkilani (Admin, Microsoft Power BI) Jun 21, 2016:

“

Thanks for suggestion this and all your comments. We’ve added this feature to our backlog but can’t share an ETA on when it’ll be delivered for now.
The current workaround we’ve been suggesting is to use the personal gateway to combine on-prem with online data sources.
I’ll update the status once this work has started. Meanwhile, keep voting and adding other ideas to improve the gateway.

”

Regarding additional information about Data refresh, please check:

Data refresh in Power BI

If any further help needed, please feel free to post back.

Regards

[MatWebb]

Ah. Thanks @v-micsh-msft - appreciate you finding that for me. That's just the answer i didnt want, but it seems to be pretty terminal.

I have stuck 3 votes and a comment on that 'idea'. Im quite shocked that this is 'working as intended' seems a VERY strange limitation of what the essence of this tool is designed to do.

I will ahve to sit on the updates and hope i guess.

Thanks all for your help!

Mat

[Seth_C_Bauer]

@MatWebb Can't find the specifics of what I thought I read. But based on the documentation on the refresh page this conflict doesn't seem surprising since you can't manually configure refresh for Azure SQL DB...

source

I'm hesitant to say this can't be done, because I can't find a clear statement pertaining to that. Have you tried moving your PBIX to a OneDrive location? Maybe pushing that up would enable the refresh...

[Seth_C_Bauer]

@MatWebb Based on your description it sounds like the issue is more with mixing on-premises data sources with cloud data sources. One requires a gateway, the other does not... I recall reading that mixing certain data sources doesn't work currently, let me see if I can find the source.