Unable to combine paged web api results and use auto refresh

[Robi2]

Hi,

I am trying to create some code that get paginated data from elastic search to create a dataset.

This works great locally but the refresh fails on my Personal Gateway on this error:

	[Unable to combine data] Section1/Users/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

I have already read that the solution for this should be separating each data call to its own function, and then creating a query that calls these functions. However, I still can't get it to work and I get the same error.

Perhaps I need to create the Power Query functions in some special way that I didn't do.

Here are my 2 functions:

let
	FnAnalyticsFirstRequest = (scroll as text, size as number) =>
		let
			Response = Json.Document(Web.Contents("http://<elasticserver>", 
					[Query=[scroll=scroll, size=Text.From(size)], RelativePath="/users/_search"]))
		in
			Response
in
	FnAnalyticsFirstRequest

let
    FnAnalyticsPagedRequest = (scroll as text, scrollId as text, cacheBust as text)=>
		let
			Response = Json.Document(Web.Contents(
				"http://<elasticsearch server>", 
				[IsRetry=true, Query=[scroll=scroll, scroll_id=scrollId], RelativePath="/_search/scroll"]))
		in
			Response
	
in
    FnAnalyticsPagedRequest

And here is my main query(Users):

let
FetchElasticsearchData= (CallsRemaining, InScrollId, ListOfResults, KeepAlive) =>
 let
 Response = Function.Invoke(FnAnalyticsPagedRequest, {KeepAlive, InScrollId, "CacheBust" & Number.ToText(CallsRemaining)}),
 OutScrollId = Response[_scroll_id],
 CombinedResults = List.Combine({ListOfResults, Response[hits][hits]})
 in
 if CallsRemaining > 0 
 then @FetchElasticsearchData(CallsRemaining- 1, OutScrollId, ListOfResults & {Response[hits][hits]}, KeepAlive)
 else ListOfResults,

 DocumentsPerRequest = 5,
 KeepAliveTime = "3m",
 StartScrollQueryResult = Function.Invoke(FnAnalyticsFirstRequest, {KeepAliveTime, DocumentsPerRequest}),
 Result = FetchElasticsearchData(Number.RoundDown(StartScrollQueryResult[hits][total]/DocumentsPerRequest), StartScrollQueryResult[_scroll_id], {StartScrollQueryResult[hits][hits]}, KeepAliveTime)

in
Result

This is how it visually looks in Power BI:

Do you know what could I be missing?

Thanks,

Robert

[prathy]

Hi,

Go to Power BI options and modify privacy levels, change them to Ignore the Privacy levels and potentially improve performance. Remeber

Security Note: Selecting Ignore the Privacy levels and potentially improve performance may improve performance; however, Power BI Desktop cannot ensure the privacy of data merged into the Power BI Desktop file.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-privacy-levels/

[Robi2]

Hi,

Thanks for the quick reply but I have already tried doing that, it has no effect and I still get the same error. I even tried changing the privacy settings for the data source and played around with all of them (private, public, organizational, none), but still the error occurs every time.

[Robi2]

I have tried many different ways to do this in code, but in the end I get the same error...

Did anyone manage to get something like this to work? Or should I assume that it is just unsupported...