AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together

[SimonSays]

I'm trying to combine a table from Microsoft Common Data Service with an API from Microsoft Cognitive Services. This works fine in Desktop but when I publish to Power BI Web Service and try refreshing the dataset I get the error:

{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"[Unable to combine data] Section1/MyEntity/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination."}}],"exceptionCulprit":1}}} Table: MyEntity.

The query for the table looks like this:

let
    Source = Cds.Entities("https://orgxxxxxx.crm4.dynamics.com/", [ReorderColumns=null, UseFormattedValue=null]),
    entities = Source{[Group="entities"]}[Data],
    MyEntity = entities{[EntitySetName="MyEntity"]}[Data],
    #"Invoked Custom Function" = Table.AddColumn(MyEntity, "Keyword", each KeywordPhrase([Phrase])),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Invoked Custom Function", {{"Keyword", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Keyword"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Keyword", type text}})
in
    #"Changed Type"

The query to extract keywords looks like this:

(text) => let
    sCmd = "/keyPhrases",
    endpoint = "https://xxxxxxxxxxxxxxxx.azurewebsites.net",
    jsontext = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),
    jsonbody = "{ documents: [ { language: ""en"", id: ""0"", text: " & jsontext & " } ] }",
    bytesbody = Text.ToBinary(jsonbody),
    bytesresp = Web.Contents(endpoint, [RelativePath=apiKey&sCmd, Content=bytesbody]),
    jsonresp = Json.Document(bytesresp),
    keyphrases = Text.Lower(Text.Combine(jsonresp[documents]{0}[keyPhrases], ", "))
in keyphrases

I followed a blog post that suggested splitting the Cds data load into a separate query but Power BI Web Service then gives me the error:

{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"[Unable to combine data] Section1/Query1/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}}],"exceptionCulprit":1}}} Table: Query1.

I'd be grateful for any advice people have.

Thanks

Simon

[GilbertQ · Accepted solution]

Hi there

Could I ask why you cannot change the privacy levels?

[SimonSays]

Hi @GilbertQ, thanks for your reponse. I tried Private, Public and Organizational privay levels for both queries but nothing worked.

[GilbertQ]

Did you try NONE?

[SimonSays]

I did yes. None of the privacy settings allowed me to refresh the dataset once published to Power BI Web Service.

[GilbertQ]

That is something not normal, I have never had an issue once trying to refresh in the Power BI Service.

It could only be with the different data sources.

And you have configured the Gateway to refresh from the cloud & on-prem data sources?

[SimonSays]

Hi @GilbertQ, I'm not using any on-prem data souces. I have a data set from a Microsoft Comon Data Services entity and I'm using a Microsoft Cognitive Services API to analyse the text in my Cds dataset. It's really a very simple setup and the queries are all in my original post. 

[SimonSays]

Hi @GilbertQ, I had overlooked the privacy settings in the BI Web Service and was making changes in desktop before publishing. I seem to have fixed the problem by changing both queries to Organizational privacy directly in the Web Service. Thank you for your help. I'll mark your reply as accepted solution.   

[Anonymous]

Hey @SimonSays 

Rigth now I'm having a similar issue. 

I’m calling a text cognitive service (keyPhrases) through a custom function, here is the code:

let
Origen = (text) => let

jsontext = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 10000))),
jsonbody = “{ documents: [ { language: “”es””, 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)
in jsonresp

in
Origen

It’s work fine in PBI Desktop, but when I deploy it to PBI Service I got this error in the data source credential:

Web.Contents failed to get contents from ‘https://tapbi01.cognitiveservices.azure.com/text/analytics/v2.1/keyPhrases’ (404): Resource Not Found

I tried your solution, but now I get this error:

Web.Contents with the Content option is only supported when connecting anonymously.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table:

I restored it back to connect anonymously and again:

{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"[Unable to combine data] Section1/MyTable/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination."}}],"exceptionCulprit":1}}} Table: MyTable

I'd appreciate any help.

[GilbertQ]

That is great that you found out the issue! Thanks for letting us know.