请求协助用Power Query筛选行并转置数据，批量导入Azure SQL Database

一、Power Query数据转换步骤 #

1. 批量加载所有Excel工作簿 #

• 打开Power BI Desktop或Excel的Power Query编辑器
• 选择「数据」>「获取数据」>「从文件」>「从文件夹」，选中存放400份Excel文件的文件夹
• 在文件夹数据预览界面，点击「合并并加载」>「合并并编辑」，进入Power Query编辑器

2. 编写自定义逻辑处理单份工作簿数据 #

为加载的文件夹数据添加自定义列，处理单个Excel文件的内容逻辑：

let
    // 加载当前工作簿的目标工作表（假设每个工作簿仅1个数据工作表，名称为Sheet1）
    LoadSheet = Excel.Workbook([Content], null, true){[Item="Sheet1",Kind="Sheet"]}[Data],
    // 提取客户名称与供应地址（假设这两项在数据最上方第1、2行）
    CustomerName = LoadSheet{0}[Column1],
    SupplyAddress = LoadSheet{1}[Column1],
    // 筛选出包含"ICP"的行，标记各ICP区块的起始位置
    ICPRows = Table.SelectRows(LoadSheet, each Text.Contains(Text.From([Column1]), "ICP")),
    // 获取所有ICP行的索引值
    ICPIndices = List.Transform(Table.ToList(ICPRows[Index]), each _),
    // 生成每个ICP区块的范围（当前ICP行到下一个ICP行之前）
    ICPRanges = List.Zip({ICPIndices, List.Skip(ICPIndices, 1) & {Table.RowCount(LoadSheet)}}),
    // 遍历每个ICP区块提取有效费率行
    ProcessICPRanges = List.Transform(ICPRanges, (range) =>
        let
            StartIdx = range{0},
            EndIdx = range{1}-1,
            // 获取当前ICP的名称
            CurrentICP = LoadSheet{StartIdx}[Column1],
            // 提取ICP区块内的行，筛选Old Rates或New Rates非空的行
            BlockData = Table.Range(LoadSheet, StartIdx+1, EndIdx - StartIdx),
            FilteredRates = Table.SelectRows(BlockData, each not (isnull([Old Rates]) or [Old Rates] = "") or not (isnull([New Rates]) or [New Rates] = "")),
            // 添加客户、地址、ICP上下文列
            AddCustomer = Table.AddColumn(FilteredRates, "客户名称", each CustomerName),
            AddAddress = Table.AddColumn(AddCustomer, "供应地址", each SupplyAddress),
            AddICP = Table.AddColumn(AddAddress, "ICP", each CurrentICP),
            // 调整列顺序匹配目标格式
            ReorderCols = Table.ReorderColumns(AddICP, {"客户名称", "供应地址", "ICP", "Old Rates", "New Rates"})
        in
            ReorderCols
    ),
    // 合并所有ICP区块的数据
    CombinedData = Table.Combine(ProcessICPRanges)
in
    CombinedData

注：需根据实际Excel的列名、客户/地址所在行位置调整代码中的Column1、行索引等参数；若工作表名称不是"Sheet1"，也要对应修改。

3. 整理并导出转换后的数据 #

• 完成自定义列处理后，删除Content、Name等无需保留的原始列
• 检查数据格式，将Old Rates和New Rates设置为数值类型
• 选择「关闭并上载」，可将数据加载到Excel/Power BI，或直接导出为CSV文件备用

二、批量导入Azure SQL Database #

方法1：Power Query直接导入 #

• 在Power Query编辑器中，选择「发布」>「发布到SQL Server」
• 输入Azure SQL Database的服务器名称、数据库名称及身份验证信息
• 指定目标表名，选择「追加数据」或「替换数据」完成导入

方法2：Bulk Insert批量导入CSV #

1. 将转换后的数据导出为单个CSV文件（按需可按客户拆分）
2. 在Azure SQL Database中创建临时表：

CREATE TABLE #PricingTemp (
    客户名称 NVARCHAR(255),
    供应地址 NVARCHAR(500),
    ICP NVARCHAR(100),
    OldRates DECIMAL(18,4),
    NewRates DECIMAL(18,4)
)

3. 使用Bulk Insert命令导入CSV（需将CSV存储在Azure Blob存储并配置外部数据源，或使用SSMS导入导出向导）：

BULK INSERT #PricingTemp
FROM 'your-blob-storage-path/pricing-data.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2, -- 跳过表头行
    CODEPAGE = 'UTF-8'
)

三、SQL端拆分正式表结构 #

根据业务需求将临时表数据拆分到多张正式表，示例：

-- 创建客户表
CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(255) UNIQUE NOT NULL
)

-- 导入客户数据
INSERT INTO Customers (CustomerName)
SELECT DISTINCT 客户名称 FROM #PricingTemp

-- 创建供应地址表
CREATE TABLE SupplyAddresses (
    AddressID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
    Address NVARCHAR(500) NOT NULL
)

-- 导入地址数据
INSERT INTO SupplyAddresses (CustomerID, Address)
SELECT c.CustomerID, pt.供应地址
FROM #PricingTemp pt
JOIN Customers c ON pt.客户名称 = c.CustomerName
GROUP BY c.CustomerID, pt.供应地址

-- 创建ICP表
CREATE TABLE ICPs (
    ICPID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
    ICPName NVARCHAR(100) NOT NULL
)

-- 导入ICP数据
INSERT INTO ICPs (CustomerID, ICPName)
SELECT c.CustomerID, pt.ICP
FROM #PricingTemp pt
JOIN Customers c ON pt.客户名称 = c.CustomerName
GROUP BY c.CustomerID, pt.ICP

-- 创建费率表
CREATE TABLE PricingRates (
    RateID INT IDENTITY(1,1) PRIMARY KEY,
    ICPID INT FOREIGN KEY REFERENCES ICPs(ICPID),
    OldRate DECIMAL(18,4),
    NewRate DECIMAL(18,4)
)

-- 导入费率数据
INSERT INTO PricingRates (ICPID, OldRate, NewRate)
SELECT i.ICPID, pt.OldRates, pt.NewRates
FROM #PricingTemp pt
JOIN Customers c ON pt.客户名称 = c.CustomerName
JOIN ICPs i ON c.CustomerID = i.CustomerID AND pt.ICP = i.ICPName

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