Power BI调用API添加Offset&Limit分页后出现列不存在及无结果问题

问题背景 #

在Power BI中通过M代码调用外部API获取数据，原代码仅能拉取10000条数据，添加Offset与Limit实现分页后，出现错误：Expression.Error: The column 'StartedDate' of the table wasn't found；移除列类型转换步骤后，无法获取任何数据。

相关M代码 #

初始代码（未分页） #

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 = "jobs", //RESTAPI endpoint
//Query = [#"sQueryKey" = QueryKey,#"FilterLocation"="Database"], // QueryParameters
Headers=[Accept="application/json", #"Content-Type"="application/json",#"API-Key"=Token, UID=GetUID, DeviceID=ApiKey] //Headers
]
)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
//display all the columns from query
Result=Table.ExpandRecordColumn( #"Converted to Table","Column1", Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(#"Converted to Table","Column1"), each _ <> "" and _ <> null))),Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(#"Converted to Table","Column1"), each _ <> "" and _ <> null)))),
#"Changed Type" = Table.TransformColumnTypes(Result,{{"StartedDate", type datetime}})

in
#"Changed Type"

分页修改后代码（报错版本） #

let
    // Function to fetch data with pagination
    Fetchjobs = (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 = "jobs",
                                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,

    // Initial pagination parameters
    Offset = 0,
    Limit = 10000,
    PageSize = 10000,

    // Loop through pages to get all records
    AllPages = List.Generate(
                    () => [Result = Fetchjobs(Offset, Limit), Offset = Offset + PageSize],
                    each Table.RowCount([Result]) > 0,
                    each [Result = Fetchjobs([Offset], Limit), Offset = [Offset] + PageSize],
                    each [Result]
                ),

    // Combine all the pages into a single table
    tbljobs = Table.Combine(AllPages),

    // Change column types as needed
    #"Changed Type" = Table.TransformColumnTypes(tbljobs,{{"StartedDate", type datetime}})
    
in
    #"Changed Type"

移除类型转换后的代码（无数据版本） #

let
    // Function to fetch data with pagination
    Fetchjobs = (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 = "jobs",
                                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,

    // Initial pagination parameters
    Offset = 0,
    Limit = 10000,
    PageSize = 10000,

    // Loop through pages to get all records
    AllPages = List.Generate(
                    () => [Result = Fetchjobs(Offset, Limit), Offset = Offset + PageSize],
                    each Table.RowCount([Result]) > 0,
                    each [Result = Fetchjobs([Offset], Limit), Offset = [Offset] + PageSize],
                    each [Result]
                ),

    // Combine all the pages into a single table
    tbljobs = Table.Combine(AllPages)//,

    // Change column types as needed
    //#"Changed Type" = Table.TransformColumnTypes(tbljobs,{{"StartedDate", type datetime}})
    tbljobs
in
    tbljobs//#"Changed Type"

问题原因 #

1. 空表结构不一致导致合并失效
   当分页到无数据的页面时，API返回空结果，Fetchjobs函数会返回仅包含Column1列的空表；但有数据的页面是已经展开所有字段的表。Table.Combine要求合并的所有表结构完全匹配，结构不兼容的表合并后会丢失所有有效字段，既导致找不到StartedDate列，也会让移除类型转换后看不到任何数据。

2. 动态列名逻辑存在漏洞
   通过Table.ColumnNames(Table.FromRecords(List.Select(...)))动态获取列名的方式，如果某一页返回的所有Column1记录为空或格式异常，会导致获取的列名集合为空，展开后无有效字段，进一步破坏表结构。

3. 分页循环终止条件错误
   List.Generate的终止条件是Table.RowCount([Result]) > 0，但空表的行数为0，循环会终止，但空表已经被加入到AllPages列表中参与合并，直接破坏了后续合并的表结构一致性。

4. 重复登录逻辑的副作用
   Fetchjobs函数每次分页请求都重复执行登录流程获取UID，不仅浪费API请求资源，还可能触发频率限制，甚至导致登录状态冲突，间接影响数据返回格式。

内容的提问来源于stack exchange，提问作者microsoftdeveloperdesigner