Composite Mode Doesn't Work

[bubbledep]

Hello everyone,

When I publish a report with only one connection as Direct Query, I can see the data clearly in the Power BI Service. But if I add any other connection as Import Query (Sharepoint or REST API) to the model, the data isn't retrieved and the message below is showned.

I tried already to publish the report with the models below:

* Direct Query and Import Query with relationship active via key column

* Direct Query and Import Query with no relationship at all

I also tried to change some permissions from the credentials. I tried "none", "public", "organizational" and "private" for all my data tables from the model, but unfortunately none of them worked.

Below I detail my data types:

1 - Warehouse that I retrieve data from a Gen2 Dataflow (Direct Query)

2 - REST API to retrieve data from Notion App (Import Mode)

3 - Sharepoint Folder link to retrieve data from Sharepoint (Import Mode)

Any ideas? Thanks to all in advance.

[v-sshirivolu]

Hi @bubbledep ,

I would also take a moment to thank @DataNinja777  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[DataNinja777]

Hi @bubbledep ,

The problem you're encountering with your composite model is a classic one and almost certainly requires an on-premises data gateway. When your report had only a single DirectQuery connection to a Gen2 Dataflow, the Power BI Service handled it seamlessly using your cloud credentials. The moment you added Import sources—your SharePoint folder and the Notion REST API—you created a scenario where the Power BI mashup engine needs a single, secure point to combine data from these distinct locations, which the gateway provides.

Think of the data gateway (standard mode) as a secure bridge that allows the Power BI Service to communicate with your import data sources.  The service can't natively combine the cloud-based DirectQuery data with data from your API or SharePoint folder without this bridge to manage the credentials and data processing securely. This is why changing the privacy levels alone didn't solve the issue; the fundamental connectivity required for the data mashup was missing.

Your first step is to install the on-premises data gateway on a computer or server that will always be running. Once installed, you'll sign in with your Power BI account to register it. Then, in the Power BI Service, navigate to Settings > Manage connections and gateways. Here, you will add your SharePoint and REST API sources as new connections on the gateway. For the SharePoint folder, you should use OAuth2 as the authentication method and sign in with your organizational account. For the Notion REST API, you will likely create a "Web" connection and set its authentication to Anonymous. This is because your secret API key is typically handled directly within your M query in Power Query rather than as a credential in the gateway settings.

After configuring the data sources on the gateway, the final and most crucial step is to map your dataset to use them. In your Power BI workspace, find your dataset's Settings and expand the Gateway and cloud connections section. You must map your SharePoint and Web data sources to the gateway connections you just created. Your Gen2 Dataflow source will remain a direct cloud connection and does not need to be mapped. Once you apply these changes and confirm a successful connection status, republish your PBIX file from Power BI Desktop. It's also a good practice to ensure the privacy levels for all sources are set to Organizational before publishing, as this allows them to share data during the refresh. Following these steps should resolve the error and allow your visuals to load correctly.

Best regards,

[bubbledep]

Hi @DataNinja777 ,

Thanks for the suggestions. Unfortunately, that solution didn't worked 100%, I'll post some details below.

When trying to configure a new connection for the Notion REST API dataset, like you suggested in "For the Notion REST API, you will likely create a "Web" connection and set its authentication to Anonymous.", I'm getting the error "Unable to create connection for the following reason: Unable to connect to the data source. Either the data source is inaccessible, a connection timeout occurred, or the data source credentials are invalid. Please verify the data source configuration and contact a data source administrator to troubleshoot this issue."

Also, when I try to configure the credentials for that dataset as "Anonymous" for authentication method and "Organizational" for privacy level, I'm getting the error "Failed to update data source credentials: Web.Contents failed to get contents from 'https://api.notion.com/v1/databases/example/query' (400): Bad Request".

Thanks for the effort and help, but do you have any other suggestions?

[v-sshirivolu · Accepted solution]

Hi @bubbledep ,

Thank you for sharing the additional details. The errors you’re seeing indicate that the current connection and authentication setup doesn’t align with what the Notion API requires. Unlike anonymous access, the Notion API needs an API token sent in the request headers for authentication. Because of this, setting the authentication to Anonymous will cause connection failures and bad request errors. To resolve this, you should use Power BI’s Web connector with a custom query that includes your Notion integration token as a Bearer token in the Authorization header. In Power Query Editor, you can use the Web.Contents function to call the API endpoint and pass the necessary headers, including the Authorization header and the Notion Version header. This way, authentication is handled correctly and should prevent the errors you’ve been encountering. Also, be sure your API request is formatted properly, especially if you’re making a POST request with a request body. For privacy levels, Organizational usually works well, but if issues persist, trying None temporarily can help with troubleshooting.

[fabric-excel]

Hi @v-sshirivolu ,

In the Power BI Desktop I did that setup, and it's all good. That error is a problem that happens in the Power BI Service.

[v-sshirivolu]

Hi @fabric-excel ,

This happens because Power BI Service handles web API connections differently than Desktop, especially for custom APIs that need a manual authentication token. Try to fix this is to split your URL into a static base and the endpoint path. For example:

let
    baseUrl = "https://api.notion.com",
    endpoint = "v1/databases/YOUR_DATABASE_ID/query",
    body = "{}",

    Source = Json.Document(
        Web.Contents(
            baseUrl,
            [
                RelativePath = endpoint,
                Headers = [
                    Authorization = "Bearer YOUR_SECRET_TOKEN",
                    #"Notion-Version" = "2022-06-28",
                    #"Content-Type" = "application/json"
                ],
                Content = Text.ToBinary(body)
            ]
        )
    )
in
    Source

Replace YOUR_SECRET_TOKEN and YOUR_DATABASE_ID with your real values. This structure helps Power BI Service refresh your data without issues.

After publishing, go to Power BI Service, Settings, Datasets, Data Source Credentials, select your web data source, set Authentication Method to Anonymous, and choose a privacy level as Organizational or Public. Save the changes. Since the token is already in the header, Power BI Service doesn’t need to handle complicated login flows. You can also update the token easily without changing the query.