MONGO DB API CONNECTION ERROR

Hi rod_puente ,

Please try below steps to fix the issue.

1. Create parameters, In Power BI Desktop, Manage Parameters --> New Parameter.

Parameter 1
Name: RangeStart
Type: Date/Time
Current Value: 01/01/2021 00:00:00

Parameter 2
Name: RangeEnd
Type: Date/Time
Current Value: Now

Note: Data type must be Date/Time, not Date.

2. Please refer below sample Base MongoDB API query and do changes your M code.

let
Source =
Json.Document(
Web.Contents(
"https://data.mongodb-api.com/app/<app-id>/endpoint/data/v1/action/find",
[
Headers = [
#"Content-Type" = "application/json",
#"api-key" = "<API KEY>"
],
Content = Text.ToBinary(
Json.FromValue([
dataSource = "Cluster0",
database = "SalesDB",
collection = "Orders"
])
),
Timeout = #duration(0, 0, 15, 0)
]
)
),

Documents = Source[documents],
ToTable = Table.FromList(Documents, Splitter.SplitByNothing()),
Expanded = Table.ExpandRecordColumn(
ToTable,
"Column1",
{"orderDate","orderId","amount"}
FilteredByDate =
Table.SelectRows(
Expanded,
each
[orderDate] >= RangeStart
and [orderDate] < RangeEnd

)
in
Expanded

3. Verify your query folding, In Power Query Editor, Right‑click FilteredByDate and Select View Native Query. If it is ENABLED, it is fine. If disabled, your API does not support server‑side filtering, you must push the filter inside the API body.

Please refer below sample MongoDB filter.

Content = Text.ToBinary(
Json.FromValue([
dataSource = "Cluster0",
database = "SalesDB",
collection = "Orders",
filter = [
orderDate = [
"$gte" = RangeStart,
"$lt" = RangeEnd
]
]
])
)

4. Configure Incremental Refresh in Model, Go to Model view -->Select your table --> Open Incremental refresh.

Configure:
Enable Incremental refresh
Store rows from the last: 3 Years
Refresh rows from the last: 5 Days

Click OK and Publish to Power BI Service

5. FIRST refresh in Power BI Service, Power BI creates partitions for 3 years of data

6. MongoDB side optimization, On MongoDB, ensure below index exists:

db.Orders.createIndex({ orderDate: 1 })

Note: Without above index, timeouts continue even with incremental refresh.

Please refer below link.

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh