本帖最后由 刀羊 于 2019-1-10 21:45 编辑
PowerQery:
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(源,{{"订单编号", Int64.Type}, {"标题", type text}, {"价格", Int64.Type}, {"购买数量", Int64.Type}, {"外部系统编号", type text}, {"商品属性", type text}, {"套餐信息", type any}, {"备注", type any}, {"订单状态", type text}, {"商家编码", type text}}),
删除的其他列 = Table.SelectColumns(更改的类型,{"订单编号", "标题", "价格", "购买数量", "外部系统编号", "商品属性"}),
按分隔符拆分列 = Table.SplitColumn(删除的其他列, "商品属性", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"商品属性.1", "商品属性.2", "商品属性.3"}),
更改的类型1 = Table.TransformColumnTypes(按分隔符拆分列,{{"商品属性.1", type text}, {"商品属性.2", type text}, {"商品属性.3", type text}}),
按分隔符拆分列1 = Table.SplitColumn(更改的类型1, "商品属性.2", Splitter.SplitTextByEachDelimiter({"色"}, QuoteStyle.Csv, false), {"商品属性.2.1", "商品属性.2.2"}),
更改的类型2 = Table.TransformColumnTypes(按分隔符拆分列1,{{"商品属性.2.1", type text}, {"商品属性.2.2", type text}}),
添加的后缀 = Table.TransformColumns(更改的类型2, {{"商品属性.2.1", each _ & "色", type text}}),
删除的列 = Table.RemoveColumns(添加的后缀,{"商品属性.1", "商品属性.2.2"}),
重命名的列 = Table.RenameColumns(删除的列,{{"商品属性.2.1", "颜色"}, {"商品属性.3", "号码"}}),
重排序的列 = Table.ReorderColumns(重命名的列,{"订单编号", "标题", "外部系统编号", "价格", "颜色", "号码"})
in
重排序的列 |