Append query failing refresh when published

[rajeshwmg]

I have a PBI Report with 2 data sources (onprem SSAS; azure sql server). Both are set up in import mode and gateway has been set up for the on-prem source. This report can be published into PBI service and refresh successfully. 

I then added an append query as new to union the two data sources. This runs fine in desktop. When published, i get the following error when refreshing: Unable to connect to the data source undefined

I do not have administrative access to the gateways. I have raised with internal support who have said that they only support the connections working for on-prem and cloud (which is already proven in the first version we published). 

I've looked through the previous forum entries and it seems that access needs to be granted to the on-prem gateway to the cloud source. Has anyone had to overcome this and what do I need to get the support team to do? 

[v-xiaoyan-msft]

Hi @rajeshwmg ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[rajeshwmg]

@GilbertQ thank you for the quick reply. When I get some time over the next couple of days I'll investigate trying this. Not used powerquery before but I'm sure I can give it a go. Also, you mentioned privacy settings, is this something on the gateway that is preventing the append from succeeding? 

[nickyvv]

Privacy settings apply to a datasource in Power Query. You can check that here:

[GilbertQ]

Hi @rajeshwmg 

What I have done in the past is to put both queries into a single table in Power Query Editor. It is a bit of a challenge to get it working but this will often work because everything is in a single table and the Privacy Level settings are not affected.