Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @GilbertQ, thanks for your reponse. I tried Private, Public and Organizational privay levels for both queries but nothing worked.
I did yes. None of the privacy settings allowed me to refresh the dataset once published to Power BI Web Service.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.