Forum Discussion
SimonSays
6 years agoRegular 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
- Hi there
Could I ask why you cannot change the privacy levels?