Forum Discussion
Power BI Service Refresh Error: Unable to combine data (Azure SQL and Google Translate API Privacy L
Hi,
Thanks for sharing the M code. Your implementation looks correct from a Power Query perspective, and using Table.Buffer() is a good optimization. However, the issue is not with the M syntax—it's with how Power BI Service handles privacy and data source combinations.
In your code:
BufferedTable = Table.Buffer(#"Source")
BufferedTable only buffers the Azure SQL data in memory. When Table.AddColumn() calls:
Web.Contents(
"https://translate.googleapis.com",
...
)
Power BI Service still sees this as combining Azure SQL with a Web data source for every row. During scheduled refresh, the Mashup Engine enforces privacy rules and blocks this combination, resulting in the "Unable to combine data" error.
A few recommendations:
- Verify that both the Azure SQL source and the Google Translate API are configured with the same privacy level (preferably Organizational) in the dataset settings.
- If you're using an On-premises Data Gateway, ensure both data sources are configured there with valid credentials.
- Table.Buffer() and RelativePath improve performance and are best practices, but they do not bypass Power BI Service privacy restrictions.
- If the dataset contains many rows, calling Web.Contents() once per row is also likely to hit refresh/performance limitations.
For production scenarios, the recommended approach is to move the translation outside Power BI, for example:
- Translate the data before loading it into Azure SQL.
- Use Azure Functions, Power Automate, Dataflows, or an ETL process to populate a translated column.
- Import the translated data directly into Power BI instead of invoking the API during refresh.
Based on the M code you've shared, this appears to be a Power BI Service privacy/mashup limitation rather than an issue with your code.
Hope this helps!
Thanks!
below M Code is using in my report
BufferedTable = Table.Buffer(#"Source"),
TranslateText = (inputText as text) as any =>
let
jsonResponse =
try Json.Document(
Text.FromBinary(
Web.Contents(
"https://translate.googleapis.com",
[
RelativePath = "translate_a/single",
Query = [
client = "gtx",
sl = "auto",
tl = "en",
dt = "t",
q = inputText
],
ManualStatusHandling = {400, 404, 500}
]
)
)
)
otherwise null,
translatedText =
if jsonResponse <> null
and List.NonNullCount(jsonResponse) > 0
and List.NonNullCount(List.First(jsonResponse)) > 0
then
try List.First(List.First(jsonResponse)){0}
otherwise null
else
null
in
translatedText,
#"Added Custom1" = Table.AddColumn(
BufferedTable,
"Translated",
each if [ResponseShortText] <> null and [ResponseShortText] <> ""
then TranslateText([ResponseShortText])
else null
)
Hi,
Thanks for sharing the M code. Your implementation looks correct from a Power Query perspective, and using Table.Buffer() is a good optimization. However, the issue is not with the M syntax—it's with how Power BI Service handles privacy and data source combinations.
In your code:
BufferedTable = Table.Buffer(#"Source")
BufferedTable only buffers the Azure SQL data in memory. When Table.AddColumn() calls:
Web.Contents(
"https://translate.googleapis.com",
...
)
Power BI Service still sees this as combining Azure SQL with a Web data source for every row. During scheduled refresh, the Mashup Engine enforces privacy rules and blocks this combination, resulting in the "Unable to combine data" error.
A few recommendations:
- Verify that both the Azure SQL source and the Google Translate API are configured with the same privacy level (preferably Organizational) in the dataset settings.
- If you're using an On-premises Data Gateway, ensure both data sources are configured there with valid credentials.
- Table.Buffer() and RelativePath improve performance and are best practices, but they do not bypass Power BI Service privacy restrictions.
- If the dataset contains many rows, calling Web.Contents() once per row is also likely to hit refresh/performance limitations.
For production scenarios, the recommended approach is to move the translation outside Power BI, for example:
- Translate the data before loading it into Azure SQL.
- Use Azure Functions, Power Automate, Dataflows, or an ETL process to populate a translated column.
- Import the translated data directly into Power BI instead of invoking the API during refresh.
Based on the M code you've shared, this appears to be a Power BI Service privacy/mashup limitation rather than an issue with your code.
Hope this helps!
Thanks!