Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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/
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.
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |