确认代码语言并修改以适配Power BI从SQL数据库导入数据

代码语言确认 #

这段代码是SQL（结构化查询语言），是数据库通用的查询语言。粘贴到Power BI的Power Query中报错，核心是代码本身存在语法/逻辑问题，且未适配Power BI的导入规则。

报错核心原因 #

1. 重复定义列：SELECT语句里重复出现了AccountNumber和CompanyName列，Power Query导入时会因列名冲突报错
2. 缺少表来源与关联：代码用了Z、a、r、rpc这些表别名，但没写FROM子句说明表的实际名称，也没定义表之间的关联逻辑，数据库无法识别数据来源
3. 注释内的聚合函数存在问题：如果要启用注释掉的MAX(z.RenewalTerm)，必须搭配GROUP BY子句，否则不符合SQL聚合查询规则

适配Power BI的修改步骤 #

• 移除重复列：删掉SELECT里重复的(z.AccountNumber) as AccountNumber和(z.CompanyName) as CompanyName
• 补充FROM与表关联：添加FROM子句，明确各个表的实际名称，以及表之间的关联条件（需根据你的实际数据库表结构调整）
• 处理聚合函数（如需启用）：如果要打开注释的MAX字段，必须在代码末尾添加GROUP BY子句，包含所有非聚合的SELECT字段
• 规范列名：把带空格的列名'Desktop Users'改成[Desktop Users]或者DesktopUsers，避免Power BI识别异常

修改后的示例代码 #

SELECT distinct  
    Z.Territory,
    z.AccountNumber as AccountNumber,
    a.AccountType as AccountType,
    z.CompanyName as CompanyName,
    z.SubscriptionReference,
    z.SubscriptionID,
    isnull(z.serialnumber,r.SerialNumber) as SerialNumber,
    case when rpc.billingperiodalignment = 'AlignToCharge' then z.product else isnull(r.ProductDescription,z.product) end as Description,
    r.ProductVersion as ProductVersion,
    r.CoverExpiryDate as CoverExpiryDate,
    z.SubscriptionStatus as SubscriptionStatus,
    z.SubscriptionVersion,
    -- MAX(z.RenewalTerm) AS RenewalTerm,
    z.[SubscriptionTermType] as [SubscriptionTermType],
    case when z.[UnitofMeasure] = 'Desktop Users' then z.[Quantity] end as [Desktop Users],
    z.billingperiod as BillingPeriod,
    rpc.BillingPeriodAlignment as BillingPeriodAlignment,
    z.[ContractEffectiveDate] as ContractEffectiveDate,
    z.TermEndDate as TermEndDate
-- 替换为你的实际表名和关联条件，示例：
FROM 你的主表名 Z
JOIN 账户表名 a ON Z.AccountID = a.AccountID
JOIN 产品表名 r ON Z.ProductID = r.ProductID
JOIN 计费配置表名 rpc ON Z.SubscriptionID = rpc.SubscriptionID
-- 如果启用MAX聚合，需添加GROUP BY，示例：
-- GROUP BY Z.Territory, z.AccountNumber, a.AccountType, z.CompanyName, z.SubscriptionReference, z.SubscriptionID, isnull(z.serialnumber,r.SerialNumber), case when rpc.billingperiodalignment = 'AlignToCharge' then z.product else isnull(r.ProductDescription,z.product) end, r.ProductVersion, r.CoverExpiryDate, z.SubscriptionStatus, z.SubscriptionVersion, z.[SubscriptionTermType], case when z.[UnitofMeasure] = 'Desktop Users' then z.[Quantity] end, z.billingperiod, rpc.BillingPeriodAlignment, z.[ContractEffectiveDate], z.TermEndDate

Power BI导入操作步骤 #

1. 打开Power BI，点击「获取数据」→「SQL Server数据库」
2. 输入数据库服务器地址和数据库名称
3. 勾选「高级选项」，把修改后的SQL代码粘贴到「SQL语句」框中
4. 点击「确定」，等待数据加载完成即可

内容的提问来源于stack exchange，提问作者A. Molinari