🚀 Stop Paying for Connectors: How to Build a Custom Power BI API Integration for Marketing Data

Manual CSV exports are the silent killer of productivity. If you're managing marketing data for Meta, Google Ads, or TikTok, you've likely seen the high prices of "middleware" connectors.

Today, I’m sharing how to build your own "pipe" directly into Power BI using Power Query (M).

The Challenge

Most marketing APIs use OAuth2. The biggest hurdle isn't getting the data; it's handling the token refresh without the dashboard breaking every 60 minutes.

The Solution (The "M" Script)

Here is a simplified version of the logic I use to fetch data from a REST API:

let
    url = "https://api.example.com/v1/reports",
    header = [Headers=[#"Authorization"="Bearer " & YourAccessToken]],
    response = Json.Document(Web.Contents(url, header)),
    data = response[data]
in
    data

Key Lessons Learned:
Pagination is Critical: Most APIs only give you 100 rows at a time. You need a recursive function in M to "loop" until all data is fetched.

JSON Flattening: Marketing APIs return deeply nested data. Use the Table.ExpandRecordColumn function to flatten your results into a usable format for DAX.

Privacy Levels: Set your privacy levels to "Organizational" in Power BI Desktop to avoid the "Formula.Firewall" error when combining API data with local files.

I’ve been specializing in these custom "no-middleware" setups to help agencies scale their reporting without the overhead.

Check out my portfolio of automated dashboards at [https://visualizexpert.com/]