cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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

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

1 ACCEPTED SOLUTION
Super User II
Super User II

Hi there

Could I ask why you cannot change the privacy levels?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

9 REPLIES 9
Super User II
Super User II

Hi there

Could I ask why you cannot change the privacy levels?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

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

Did you try NONE?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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. 

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.   

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.

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors