时间戳

首页 > 解决方案 > 如何将excel列拆分为多行

excel - 如何将excel列拆分为多行

问题描述

我从活动注册表中获取导出，其中每个订单（行）包含订单日期、购买价格和 1-4 名注册人的信息。每个注册人都有名字、姓氏和出生日期的列。我需要的是拆分这些行，以便每个人都在他们自己的行上，第一行、最后一个和 DOB 在行上，理想情况下，其他订单信息也重复。

这可能会令人困惑，所以我有导出表格的模型日期，以及我想将它们转换成的表格。

我得到了什么： https ://drive.google.com/file/d/1xt64Re2CTlbQnHuRy1dQaFeNV5OmETfW/view?usp=sharing

我想要什么： https ://drive.google.com/file/d/156WmiQ4Tx4JGB5FYLTqHBuVmJqi20pRZ/view?usp=sharing

我发现本教程似乎非常接近我想要使用 Excel Power Query 的内容，但它描述了一种方法，用于当多个项目位于一个以逗号分隔的列中时。我的情况有点不同，我无法让它发挥作用。

有任何想法吗？

标签： excelexcel-formulagoogle-sheets-formulapowerquerygoogle-sheets-query

解决方案

当加载到范围Table1中时，PowerQuery 下面适用于您拥有的示例数据集

为任意数量的 First/Last/DOB 列集动态编写；你有 4 个，这可以处理任意数量的

粘贴到主页...高级编辑器...

有点冗长以分开步骤并使其可见

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}}),

// Get names of all First, Last and DOB fields
ColumnsToCombine1 = List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_, "(First)")),
ColumnsToCombine2 = List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_, "(Last)")),
ColumnsToCombine3 = List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_, "DOB")),

//Convert to Text, so we can merge columns; for date fields, convert to date first
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type", (List.Transform(ColumnsToCombine3 ,each {_,type date}))),
#"Changed Type2" = Table.TransformColumnTypes (#"Changed Type1",List.Transform(Table.ColumnNames(#"Changed Type1"), each {_, type text})),

// convert all First, Last and DOB fields into three comma separated columns
#"Combined1" = Table.AddColumn(#"Changed Type2" , "First", each Text.Combine(Record.FieldValues(Record.SelectFields(_,ColumnsToCombine1)),", ")),
#"Combined2" = Table.AddColumn(#"Combined1", "Last", each Text.Combine(Record.FieldValues(Record.SelectFields(_,ColumnsToCombine2)),", ")),
#"Combined3" = Table.AddColumn(#"Combined2", "DOB", each Text.Combine(Record.FieldValues(Record.SelectFields(_,ColumnsToCombine3)),", ")),

#"Removed Other Columns" = Table.SelectColumns(Combined3,{"ID", "Order Date", "Quantity", "Payment", "First", "Donate", "Last", "DOB"}),

//expand all comma separated columns into their own rows
TableTransform = Table.Combine(List.Transform(List.Transform(Table.ToRecords(#"Removed Other Columns" ), (x) => List.Transform(Record.ToList(x), each Text.Split(_,","))), each Table.FromColumns(_, Table.ColumnNames(#"Removed Other Columns" )))),

#"Reordered Columns" = Table.ReorderColumns(TableTransform,{"ID", "Order Date", "Quantity", "First", "Last", "DOB", "Payment", "Donate"}),
#"Filled Down" = Table.FillDown(#"Reordered Columns",{"ID", "Order Date", "Quantity", "Payment", "Donate"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Filled Down",{{"DOB", type date}, {"Order Date", type date}, {"Payment", type number}, {"Donate", type number}, {"Quantity", type number}, {"First", type text}, {"Last", type text}})
in #"Changed Type3"

替代版本，硬编码为 4 个表格，基于 Ron 的

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
colNames = Table.ColumnNames(Source),
Table1 = Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source,List.RemoveMatchingItems(colNames , List.Select(colNames, each Text.Contains(_, "-2") or Text.Contains(_, "-3") or Text.Contains(_, "-4"))))),1),
Table2 = Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source,List.RemoveMatchingItems(colNames , List.Select(colNames, each Text.Contains(_, "-1") or Text.Contains(_, "-3") or Text.Contains(_, "-4"))))),1),
Table3 = Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source,List.RemoveMatchingItems(colNames , List.Select(colNames, each Text.Contains(_, "-1") or Text.Contains(_, "-2") or Text.Contains(_, "-4"))))),1),
Table4 = Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source,List.RemoveMatchingItems(colNames , List.Select(colNames, each Text.Contains(_, "-1") or Text.Contains(_, "-2") or Text.Contains(_, "-3"))))),1),
combined = Table1  & Table2 & Table3 & Table4,
#"Filtered Rows" = Table.SelectRows(combined, each ([Column4] <> null)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Column1", Order.Ascending}}),
#"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Column2", "Order Date"}, {"Column3", "Quantity"}, {"Column4", "First"}, {"Column5", "Last"}, {"Column6", "DOB"}, {"Column7", "Payment"}, {"Column8", "Donate"}, {"Column1", "ID"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"ID", type number}, {"Quantity", type number}, {"Payment", type number}, {"Donate", type number}, {"Order Date", type date}, {"DOB", type date}, {"First", type text}, {"Last", type text}})
in #"Changed Type"

推荐阅读