Raiser's Edge Custom Connector Data Loading Issues

Hi wemmeLongi , Thank you for reaching out to the Microsoft Community Forum.

The difference in time may come down to how Power BI processes the data. It adds overhead by automatically detecting data types, expanding nested fields and applying transformations, things Power Automate skips. It also fetches pages sequentially and if Blackbaud enforces rate limits, Power BI may pause and retry, slowing things down further. If you're using the Power BI Service, your data gateway might be contributing to the delay as well.

Start by optimizing your Power BI query. In Power BI Desktop, open the Query Editor, review the Applied Steps and remove any unnecessary transformations, especially expanding records or sorting, which are best done after the data loads. Then replace your query with this efficient pagination script:

let

    PageSize = 500,

    GetPage = (offset as number) =>

        let

            Source = Json.Document(Web.Contents("https://api.sky.blackbaud.com/gift/v1/gifts", [

                Headers = [#"Bb-Api-Subscription-Key"="YOUR_KEY", Authorization="Bearer YOUR_TOKEN"],

                Query = [start_gift_date="2022-06-30", limit=Text.From(PageSize), offset=Text.From(offset)]

            ]))

        in

            Source[value],

    AllPages = List.Generate(

        () => [offset = 0, page = GetPage(0)],

        each List.Count([page]) > 0,

        each [offset = [offset] + PageSize, page = GetPage([offset])],

        each [page]

    ),

    Combined = List.Combine(AllPages),

    Output = Table.FromList(Combined, Splitter.SplitByNothing(), {"gift"})

in

    Output

This minimizes processing and loops efficiently through the pages. To check performance, try running it with a smaller date range to isolate whether the issue is data volume or something else. If it’s still slow, use Postman to test the same API call and see if you’re hitting rate limits (look for 429 errors). If so, reduce the batch size in the script from 500 to 100 and test again. If you're refreshing through Power BI Service, compare the speed to Power BI Desktop. If Desktop is much faster, the gateway may be slowing things down, check that it’s updated and not under heavy load.

Since Power Automate performs well, another reliable option is to use it to pull the data into a CSV file (e.g., on OneDrive) or a SQL database. Then connect Power BI to that file or source instead of querying the API directly. This offloads the slow part of the process and lets Power BI just read already-staged data.

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

Hi wemmeLongi 

The performance discrepancy you're seeing between the Raiser's Edge custom Power BI connector and the Power Automate connector—even though both use the same API endpoint and batching approach—likely stems from how Power BI handles data retrieval under the hood. Power BI’s Power Query engine is optimized for flexibility and transformation rather than raw speed, and when it makes API calls, it typically does so sequentially rather than in parallel, unless custom code or a custom connector explicitly enables parallelism. In contrast, Power Automate workflows can be more efficient at processing API calls in parallel or in batches more quickly, especially if the flow is designed for performance and minimal transformation overhead. Furthermore, Power BI may be doing additional metadata inspection, type conversion, or refresh validation during the data load, which can add significant overhead compared to the relatively lightweight execution of Power Automate. If you're using a custom Power BI connector, ensure that it’s optimized for pagination and consider using techniques such as Table.Buffer, disabling unnecessary schema validations, or enabling concurrent calls if your connector supports it. Otherwise, the bottleneck may persist due to Power BI’s more transformation-heavy and sequential query execution model.