Scheduled refresh known bug (multiple sources in one query)

[Anonymous]

The PBI Service scheduled refresh does not understand how to deal with multiple sources in a single query.

This is a bug, which manifests as a misidentified Privacy level error online and often with the following non-descript refresh error:
'Unable to connect to data source undefined'

However it is not - Desktop is able to refresh and publish without issues.
Please fix the service. Contact me if you feel this is an inadequate explanation and needs more clarity.

[v-yuezhe-msft]

@Anonymous ,

What are the data sources of your Power BI report? Do you use gateway to refresh the Power BI report? If so, please share gateway log here.

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft , the data sources are simple excel table sources, which are loaded in one query, appended within it from their own respective last steps. privacy levels are all set to none and privacy is ignored on the file and global settings. I am trying to use a personal gateway - and it used to go well, when all those sources were not in different steps on the same queries, but separate queries before appends and merges (PBI desktop used to always break in showing outputs otherwise). so yes, I am trying to refresh using a personal gateway, through the service and it ends up giving me a bs error, which is totally unrelated, for instance (privacy levels are ignored, as I mentioned; and what is AutoRemovedColumns1 anyway???):

Last refresh failed: Thu Mar 14 2019 08:33:58 GMT+0200 (Eastern European Standard Time)
Unable to connect to the data source undefined.Hide details
Underlying error code: -2147467259 Table: mainsource1.
Underlying error message: [Unable to combine data] Section1/mainsource1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.MashupSecurityException.DataSources: [{"kind":"File","path":"V:\\mainsource1.xlsx"},{"kind":"File","path":"V:\\mainsource(historical).xlsx"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason: PrivacyError
Cluster URI: WABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Activity ID: c54e7f97-e142-45a7-a487-1afa9ecba55c
Request ID: d25ec9df-86dc-abee-008d-a784f315a2c7
Time: 2019-03-14 06:33:58Z

the source names have been changed to protect the innocent (syke).

anything else?

[Anonymous]

@v-yuezhe-msft bump

[Anonymous]

@v-yuezhe-msft bump

[v-yuezhe-msft]

@Anonymous ,

What steps do you take in your Power BI Desktop query editor? Would you mind sharing me PBIX file and Excel file to test?

Regards,
Lydia

[Anonymous]

Hi, Lydia

I can't share it because of the hassle, however it can be replicated very easily by using two excel sources in one query in separate steps and then joining them also within the same query.
The problem does not exist for tables, but it exists for multiple files.
Import an excel in one step, import another in the consecutive one, then append/merge them all in the same query - and try to refresh with a gateway.

Below are my steps - which are quite simple, yet when in a single query the gateway tells me that this query has conflicts with itself about privacy levels, which is BS.

[Anonymous]

@v-yuezhe-msft 

Any bright ideas?
The Personal gateway understands at least how to combine tables but not separate files, however Enterprise can't even combine tables (I've just tested this out). It still gives me the same PrivacyError:

Underlying error message:	Information is needed in order to combine data
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259

Microsoft.Data.Mashup.MashupSecurityException.Reason:	PrivacySetting
Cluster URI:	WABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Activity ID:	102aff3c-a239-42ed-b4e2-29c2134202ff
Request ID:	c1be8e6d-ade5-25d5-cdc3-4df3d6cf240d
Time:	2019-04-09 13:19:05Z

Are you kidding me?

Also, I've seen this error exists since 2015 in these forums.

EDIT: Apr-10-2019
I found someone's post on here to try and set Organisational privacy on all sources and it worked. Why is this not mentioned ANYWHERE on this topic, MicroSoft???!
Why do I have to set privacy by force?

[iplaygod]

@Anonymous 
I have received this same error sometimes: 'Unable to connect to data source undefined'
and in a similar situation (joining queries in power query etc).

Sometimes the error "comes and goes", and sometimes it doesnt go away.

Solutions that I have found to be successful are:
# set the gateway service to restart every day.
for some unexcplicable reason the gateway service seems to "go sour" by itself and feels better after a restart. I cannot scientifically prove this, but my error have been DRASTICALLY reduced after I started restaring the gateway service every day.
Restarting the service can be done with the windows scheduler and set to custom times per day etc.
Just make sure you dont set it to restart the service during a time when you expect the PBI service to call a refresh of data sources!
# (this one sucks but its the only thing that fixes it sometimes)
delete the report files + datasets from the Power BI service, then re-publish the report (reupload) and add the data sources to the gateway in the PBI service (and recreate the refresh schedules)

Hope this helps