通过Power Query从Salesforce获取关联表RO__r指定列的技术求助

不用直接尝试展开整个RO__r嵌套表，通过以下修改可以高效获取你需要的两列，同时避免展开失败的问题：

let
    Source = Salesforce.Data("https://xxxxxx.salesforce.xxxxxx.com", [CreateNavigationProperties=true]),
    SR__c = Source{[Name="SR__c"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(SR__c,{"Name","CreatedDate","SN__c","Customer__c","Customer_Num__c","Vend__c","MRS__c","MRR__c","MS__c","MR__c","QB__c","IS__c","IPS__c","IC__c","CPR__c","PGII__c","RO__r"}),
    // 替换成你实际需要的RO__r列名
    #"Extract Target RO__r Columns" = Table.TransformColumns(#"Removed Other Columns", {
        {"RO__r", (nestedTable) => 
            try Table.SelectColumns(nestedTable, {"列名1__c", "列名2__c"}) 
            otherwise null, 
            type table [列名1__c=any, 列名2__c=any]}
    }),
    // 展开提取后的嵌套表，扁平化数据
    #"Expanded RO__r" = Table.ExpandTableColumn(#"Extract Target RO__r Columns", "RO__r", {"列名1__c", "列名2__c"}, {"RO_列名1", "RO_列名2"})
in
    #"Expanded RO__r"

关键说明： #

• 把代码中的列名1__c和列名2__c替换成你实际要从RO__r获取的列名
• try...otherwise null用来处理RO__r为空的情况，防止查询报错中断
• 如果RO__r是一对一关联（每条SR__c对应唯一一条RO__r），可以把提取逻辑简化为nestedTable{0}[列名1__c]，但用Table.SelectColumns的方式更通用，能适配一对多的关联场景

内容的提问来源于stack exchange，提问作者Sam R.