What is the exact limitation of getting data from external api? number of items or size?

[mvcsharepointde]

I have this Query inside my model view to get all data from external api using paginations (where currently the api only allow to get 10,000 items per call, so that why i am doing the pagination):-

let
    // Function to fetch data with pagination
    FetchTimeLedgerEntries = (Offset as number, Limit as number) as table =>
        let
            body = Text.Combine({"sAPIKey=", ApiKey}),
            SourceAPILogin = Json.Document(
                                Web.Contents(
                                    SmartAPI,
                                    [
                                        RelativePath="apilogin",
                                        Headers=[Accept="application/json", #"Content-Type"="application/x-www-form-urlencoded",#"API-Key"=Token],
                                        Content=Text.ToBinary(body)
                                    ]
                                )
                            ),
            tblGetUID = Table.FromList(SourceAPILogin, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            expGetUID = Table.ExpandRecordColumn(tblGetUID, "Column1", {"UID", "LanguageCode", "DatabaseVersion"}, {"Column1.UID", "Column1.LanguageCode", "Column1.DatabaseVersion"}),
            GetUID = expGetUID{0}[Column1.UID],
            Source = Json.Document(
                        Web.Contents(
                            SmartAPI,
                            [
                                RelativePath = "timeledgerentry",
                                Query = [#"offset"=Text.From(Offset), #"limit"=Text.From(Limit)], // Adding Offset and Limit parameters
                                Headers=[Accept="application/json", #"Content-Type"="application/json",#"API-Key"=Token, UID=GetUID, DeviceID=ApiKey] 
                            ]
                        )
                    ),
            tblAll = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            tableHasRecords = Table.RowCount(tblAll) > 0,
            Result = if tableHasRecords then
                        Table.ExpandRecordColumn(tblAll,"Column1", Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(tblAll,"Column1"), each _ <> "" and _ <> null))),Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(tblAll,"Column1"), each _ <> "" and _ <> null))))
                     else
                        #table(Table.ColumnNames(tblAll), {})
        in
            Result,

    // Loop through pages to get all records
    Offset = 0,
    Limit = 10000,
    PageSize = 10000,
    AllPages = List.Generate(
                    () => [Result = FetchTimeLedgerEntries(Offset, Limit), Offset = Offset + PageSize],
                    each Table.RowCount([Result]) > 0,
                    each [Result = FetchTimeLedgerEntries([Offset], Limit), Offset = [Offset] + PageSize],
                    each [Result]
                ),
    tblTimeLedgerEntries = Table.Combine(AllPages),
    #"Changed Type" = Table.TransformColumnTypes(tblTimeLedgerEntries,{{"StartTime", type datetime}, {"EndTime", type datetime}, {"LogStart", type datetime}, {"LogEnd", type datetime}})
in
    #"Changed Type"

currently i am getting all data around 51,000++ items, but in the future as we will have more items, what will be the limtation? is it on the number of items? or on the data size?

Thanks

[v-jiewu-msft · Accepted solution]

Hi @mvcsharepointde ,

Based on the description, getting data from external api have items limitation.

You can also view the following links to learn more information.

Solved: Api limit of 1000 records - getting the next 1000 ... - Microsoft Fabric Community

Solved: REST api request in Power Query more than 1000 rec... - Microsoft Fabric Community

How to overcome API call row limit? - Microsoft Fabric Community

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mvcsharepointde]

@SaiTejaTalasila

But based on my reading i read those limitations not sure if they are still valid:- 

• Free users have a maximum 1 GB data capacity.
• Pro users of Power BI Pro have 10 GB maximum capacity.
• Pro users can create groups, with a maximum 10 GB data capacity each.

Thanks

[SaiTejaTalasila]

Hi @mvcsharepointde ,

Whatever you have mentioned applies to the datamodel& workspace.I don't think those things will effect you power query.

Thanks,

Sai Teja 

[SaiTejaTalasila]

Hi @mvcsharepointde ,

As per my knowledge it depends on your data source limitations.At power bi end I don't think we have any limitations even if we have any limitations still it's possible to handle it my making few changes.

I hope it will be helpful.

Thanks,

Sai Teja