Forum Discussion
Define and invoke function within query
That would be done like so:
let AssessmentsInfoFunction = (AreaAPI as text) as list => let Source = Json.Document(Web.Contents(“https://abc.xxx.com”, [ RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria” ] )) in Source, Source = {ClientDetails[RootAreaGuid]}, #"ListOfLists" = List.Combine(Source), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "AreaGuidsForFunction"}}), #"Expanded AreaGuidsForFunction" = Table.ExpandListColumn(#"Renamed Columns", "AreaGuidsForFunction"), #"Added Custom1" = Table.AddColumn(#"Expanded AreaGuidsForFunction", "Custom", each AssessmentsInfoFunction([AreaGuidsForFunction])), #"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter), #"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"}) in #"Expanded ClientDetails"
The article I referenced in my previous post states that you have to use query parameters to make this successful:
so instead of this:
Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),
you have to write it like so:
Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json",
Query=[latlng=addresslocation, key=Keyhasbeeneditedout]])),Hi ImkeF,
Thank you for this, I made the changes as advised and it works for PowerBI deskop but still errors in the PowerBI cloud service.
I've read alot of forms and articles, (saw you also commented in a few I read - nice one).
A college has shared an excel document from their one drive for business and shared it with me. The link takes me to the sharePoint destination.
The excel document has been successfully imported with a working custom function to find the addresses from lat's and long's (
-22.338304000000001,30.042366999999999) | PowerBI Desktop
I used the web connection to the excel document as per,
https://www.youtube.com/watch?v=t4TzHu8THoA
This all works perfectly on the PowerBI desktop but keeps failing in the PowerBI cloud service.
From my understanding both sources are external
1.) SharePoint (which is the onedrive saved location)
2.) Google API call to retieve
The following additional steps where test
1.) Non Staged Query
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
This is the error from the PowerBI Cloud Service:
##[Unable to combine data] Section1/Query1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination##
2.) Staged Query as per https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
This is the error from the PowerBI Cloud Service:
##[Unable to combine data] Section1/Sheet1 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination##
3.) Excel document was moved to sharedfolder + to "c:\PorjectFolder\data.xlsx | Both results are as follows
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
This is the error from the PowerBI Cloud Service: Note - A Personal gateway was used and this is a NON Staged data query.
| -2147467259 Table: Sheet1. | |
| Underlying error message: | [Unable to combine data] Section1/Sheet1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination. |
| DM_ErrorDetailNameCode_UnderlyingHResult: | -2147467259 |
Conclusion:
It all works via PowerBI Deskop and fails on PowerBI cloud.
External sources | Failed
Moved source to a local folder via the personal gateway | Failed
Hi @Rayno & Anonymous
I suggest you read this article: https://social.technet.microsoft.com/Forums/en-US/ca434e2d-88fe-4962-b46a-a1db51e8bd89/feedback-wanted-behind-the-scenes-of-the-data-privacy-firewall?forum=powerquery
give good feedback and try to motivate Ehren to continue his series, where he will hopefully come up with the solution-part as well ;)
Maybe you get some ideas how to tweak your code further, but considering the complexity of the matter, I don't see how I can help you any further in the forum here unfortunately.
Thanks ImkeF for the link to Ehren's series and your willingness to give feedback to the community.
This issue has been extremely frustrating and I've spent countless hours trying to figure it out without success.
Thanks ImkeF , that link definitely helped.
I always say " We always tell computers what to do" so if it works on PowerBI Desktop(As example we told it to ignore Privacy levels) then there must be someone( a developer :smileyhappy: ) that didnt tell the PowerBI cloud service to listen to our Privacy settings within our PowerBI Pbix file with regards to external data sources.
So how we overide it, by telling the PowerBI cloud service what to do, we manually Add the sources to the enterprise gateway,
The enterprise gateway allows one to choose under advance setting the Privacy level which in turns corrects the Privacy level.So now it works and I've confirmed data changes.
I believe this needs to be corrected by the PowerBI team.
My idea, they need to add the ability to either take in the Privacy level configured within the pbix file or they need to provide the ability to set the Privacy level under the Data Set setting -> Data Source Credentials.
Otherwise for now, add the source connections to the enterprise gateway and set the Privacy Levels.
Thank you again for the article and for your guidance.
Kind regards,Rayno
PS, Dont stage the source information when trying the now mentioned workaround.
It didnt work for me, so I rolled back to the orginal which was to,
Invoke the function within the same DataqueryKind regards,
Rayno