从Excel迁移到Power BI后SOAP连接器Power Query性能骤降求助

我正在搭建首个Power BI配置，用自定义Power Query M脚本通过SOAP API获取数据。为方便反复测试，先在熟悉的Excel中实现：小查询耗时约1秒，大查询需2-3分钟，2个超大查询加载需20分钟（因仅需每年更新几次，这个耗时可接受）。

但切换到Power BI Desktop后出现问题：最初将Excel文件导入Power BI时，性能表现和Excel中相近；但把Power Query脚本直接复制到Power BI Desktop后，性能暴跌50倍——简单查询现在需要1-2分钟，超大查询加载1小时仍未完成，只能取消。

请问这是什么原因？

相关SOAP查询代码说明 #

以下是对象查询的SOAP代码（这类查询返回500-4000条记录，取决于对象类型）。我创建了1个递归函数getTEobjects（因为API单次SOAP调用最多返回1000条对象记录），以及2个配置表（TEobjectFields和TEobjectRelations，用于定义每种对象类型需请求的字段和关联关系）。全局变量test用于切换测试与生产环境。

(objectType as text,  optional loop as number, optional maxLoop as number, optional result as table) =>
let
 
    loop = if loop is null then 0 else loop,

    produrl = "https://_here_goes_the_PROD_url_",
    testurl = "https://_here_goes_the_TEST_url_",
    baseurl = if test then testurl else produrl,

    username="*****",
    password = "####",
    certificateKey="abracadabra",
    
    //define api call info
    apiEndPoint = "findObjectsExtended",
    apiReplyLimit = 1000,
    apiReplyCount = "totalnumberofobjects",

    //first register and get registrationKey
    SOAPEnvelope_begin = 
    "<soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:ver=#(0022)http://www.timeedit.se/timeedit3/version3#(0022)>
    <soapenv:Header/>
    <soapenv:Body>
    ",
    SOAPEnvelope_end = 
    "</soapenv:Body>
    </soapenv:Envelope>",

    SOAPEnvelope_register = 
    "<ver:register>
        <!--Optional:-->
        <ver:certificate>" & certificateKey & "</ver:certificate>
    </ver:register>
    ",

    options_register = [
        #"Content-Type" = "text/xml;charset=UTF-8"
    ],

    Source_register = Web.Contents(
        baseurl,
        [
            RelativePath = "register" ,
            Content = Text.ToBinary(SOAPEnvelope_begin & SOAPEnvelope_register & SOAPEnvelope_end)
        ]
    ),

    applicationKeyTable = Xml.Tables(Source_register){0}[Table]{0}[Table]{0}[Table]{0}[Table]{0}[Table],
    applicationKey = applicationKeyTable{0}[applicationkey],

    //Now do the actual API call
    SOAPEnvelope_login =  "
    <ver:login>
        <username>" & username & "</username>
        <password>" & password & "</password>
        <applicationkey>" & applicationKey & "</applicationkey>
    </ver:login>
    ",

    SOAPEnvelope_objects = "    
         <!--Optional:-->
         <ver:type>" & objectType & "</ver:type>" &
"          <ver:returnfields>
" &  List.Accumulate(Table.ToList(Table.SelectColumns(TEobjectFields,objectType)),"",(state,current)=>let fld=if Text.Length(current)=0 then "" else "<field>" & current & "</field>" in state & fld) &
"          </ver:returnfields>
          <ver:relatedobjecttypes>
"  & List.Accumulate(Table.ToList(Table.SelectColumns(TEobjectRelations,objectType)),"",(state,current)=>let fld=if Text.Length(current)=0 then "" else "<type>" & current & "</type>" in state & fld) &
"          </ver:relatedobjecttypes>
    <ver:beginindex>" & Number.ToText(loop * apiReplyLimit ) & "</ver:beginindex>
    <ver:numberofobjects>" & Number.ToText(apiReplyLimit) & "</ver:numberofobjects>
    ",

    options_objects = options_register,

    cnt= Text.ToBinary(SOAPEnvelope_begin & "<ver:" & apiEndPoint & ">" & SOAPEnvelope_login & SOAPEnvelope_objects & "</ver:" & apiEndPoint & ">"  & SOAPEnvelope_end),
    
    Source_Objects = Xml.Tables(Web.Contents(baseurl, [RelativePath=apiEndPoint, Content=cnt])),
    //find the real result table bu navigating down 5 levels
    findObjectsTbl = Source_Objects{0}[Table]{0}[Table]{0}[Table]{0}[Table]{0}[Table],

    //extract the total number of records
    objectsCnt = Table.TransformColumnTypes(findObjectsTbl,{{apiReplyCount, Int64.Type}}),
    totalnumberofobjects = objectsCnt[totalnumberofobjects]{0},
    //calc maxLoop: if null then first call, so initialise based on qty received. Max replies per SOAP call=apiReplyLimit
    maxLoop = if maxLoop is null then  Number.RoundUp(totalnumberofobjects/apiReplyLimit) else maxLoop,
    
        //extract list with objects 
        objectsRaw = findObjectsTbl{0}[objects]{0}[object],
        objectsLijst = objectsRaw,
    
        result = if loop = 0 then objectsLijst else Table.Combine({result,objectsLijst}),
    
        output = 
            if (loop+1)<maxLoop then 
                @getTEobjects(objectType,loop+1,maxLoop, result)
            else
                result,

    //extract the requested fields from the 'fields' sub-table in the fields attribute
    b2 = List.Accumulate(Table.ToList(Table.SelectColumns(TEobjectFields,objectType)),output,(state,fieldName)=>
        let  out = if Text.Length(fieldName)>0 
            then 
                Table.AddColumn(if Table.HasColumns(state,{fieldName}) then Table.RemoveColumns( state,{fieldName}) else state,fieldName,each extractValueFromTEFields(fieldName,[fields]))
            else
                state
        in out),
    
    //test: expand relations. Not implemented so far since ertain 'related' fields are empty resulting in error
    b3 = if Table.HasColumns(b2,{"related"}) 
        then Table.ExpandTableColumn(b2, "related", {"object"}, {"related.object"})
        else b2

in
    b2
    //b3

查询调用示例 #

以下调用在Excel中耗时约1秒，在Power BI Desktop中需约1分钟：

let
    Bron = getTEobjects("course")
in
    Bron

注：大查询和超大查询采用相同架构，但调用其他SOAP接口，数据量为1万至40万条记录。

内容的提问来源于stack exchange，提问作者Christof De Backere