Forum Discussion

SimonSays's avatar
SimonSays
Regular Visitor
6 years ago
Solved

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

  • Hi there

    Could I ask why you cannot change the privacy levels?

9 Replies

  • Hi there

    Could I ask why you cannot change the privacy levels?
    • SimonSays's avatar
      SimonSays
      Regular Visitor

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