526互联

多个中式表格合并并转换（Power Query）

发布时间 2023-04-16 18:16:28作者: 熬肥妖

数据源：

转换结果：

let
    源 = Excel.Workbook(File.Contents(".....xlsx")),
    整理表 = Table.SelectColumns(Table.SelectRows(源, each ([Kind] = "Sheet") and Text.EndsWith([Name], "区")),{"Data"}),
    展开表 = Table.ExpandTableColumn(整理表, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13"}),
    前三列向下填充 = Table.FillDown(展开表,{"Data.Column1", "Data.Column2", "Data.Column3"}),
    删除多余数据行 = Table.SelectRows(Table.PromoteHeaders(Table.Skip(前三列向下填充,3)), each ([经销商全称] <> "经销商全称") and ([区域] <> "经销商11月份库存登记表")),
    逆透视 = Table.UnpivotOtherColumns(删除多余数据行, {"区域", "办事处", "经销商全称"}, "属性", "值"),
    整理属性列 = Table.TransformColumns(逆透视, {"属性", each Text.BeforeDelimiter(_, "_")}),
    添加索引 = Table.AddIndexColumn(整理属性列, "索引"),
    两行同数 = Table.TransformColumns(添加索引, {"索引", each Number.RoundDown(_ / 2)}),
    分组透视列 = Table.Group(两行同数, {"索引"}, {"内容", each Table.Pivot(_, List.Distinct(_[属性]), "属性", "值", List.Sum)}),
    展开 = Table.ExpandTableColumn(分组透视列, "内容", {"区域", "办事处", "经销商全称", "产品日期", "数量"}, {"区域", "办事处", "经销商全称", "产品日期", "数量"}),
    删除多余列 = Table.RemoveColumns(展开,{"索引"}),
    更改的类型 = Table.TransformColumnTypes(删除多余列,{{"区域", type text}, {"办事处", type text}, {"经销商全称", type text}, {"产品日期", Int64.Type}, {"数量", Int64.Type}})
in
    更改的类型