数据源：
转换结果：
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
更改的类型标签：经销商,Table,Power,中式,索引,each,Query,Data,全称 From： https://www.cnblogs.com/officeplayer/p/17323756.html