Formula Firewall Query Problem with Text Analytics

[TonyForelle]

Hi guys, 

I tried the text analysis via Power BI:

= (text) => let
apikey = "myAPIKEY",
endpoint = "https://westeurope.api.cognitive.microsoft.com/text/analytics" & "/v3.0/keyPhrases",
jsontext = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),
jsonbody = "{ documents: [ { language: ""en"", id: ""0"", text: " & jsontext & " } ] }",
bytesbody = Text.ToBinary(jsonbody),
headers = [#"Ocp-Apim-Subscription-Key" = apikey],
bytesresp = Web.Contents(endpoint, [Headers=headers, Content=bytesbody]),
jsonresp = Json.Document(bytesresp),
keyphrases = Text.Lower(Text.Combine(jsonresp[documents]{0}[keyPhrases], ", "))
in keyphrases

which I then used on my previous Query and this came out: 

let
Quelle = Sql.Database("datawarehouse.database.windows.net", "data_warehouse", [Query="SELECT CM.createdDateTime 'date', T.displayName 'team',TC.displayName 'channel' , U.displayName 'user', CM.bodyContent 'content'#(lf)FROM ChannelMessage CM#(lf)LEFT JOIN TeamChannel TC on CM.channelID = TC.channelID#(lf)LEFT JOIN Team T on CM.teamID = T.teamID#(lf)LEFT JOIN [User] U ON CM.fromUserID = U.userID#(lf)#(lf) #(lf)#(lf)WHERE charindex('CVC 20 Group',T.displayName)>0", CreateNavigationProperties=false]),
#"Aufgerufene benutzerdefinierte Funktion" = Table.AddColumn(Quelle, "Abfrage2", each Abfrage2([content]))
in
#"Aufgerufene benutzerdefinierte Funktion"

this works, as long as I have the data sources marked as public (but only in Query Editor, as asked below). can somebody help me put these two queries together, so I can switch both the privacy levels back to private or organisational?

I have already tried the solution suggested here: 

http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

but can't really come around this. 

Another thing that happens when I have all the settings in data source set to public this message pops up:

exception of type 'microsoft.mashup.engine.interface.resource access forbiddenexception' was thrown

I have already deleted all the global settings and logged in, but the error keeps popping up. 

The Text Analysis so only works in Query Editor but couldnt be visualized yet. 

Thanks for any help!!

[v-alq-msft]

Hi, @TonyForelle 

You may find the Privacy Levels setting in File > Options and settings > Options and then Current File > Privacy determines whether Power BI Desktop uses your Privacy Level settings while combining data.

Privacy Levels is set to Combine data according to your Privacy Level settings for each source by default, which means that Privacy Levels are enforced.

CONFIGURE PRIVACY LEVELS

Setting	Description
Combine data according to your Privacy Level settings for each source (on, and the default setting)	Privacy level settings are used to determine the level of isolation between data sources when combining data.
Ignore the Privacy levels and potentially improve performance (off)	Privacy levels are not considered when combining data, however, performance and functionality of the data may increase.

For further information, you may refer to the following document.

Power BI Desktop privacy levels 

Best Regards

Allan

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