用PQ,简化了一下代码。- let
- 源 = Excel.CurrentWorkbook(){[Name = "表1"]}[Content],
- 向下填充 = Table.FillDown(源, {"列2"}),
- 替换的值 = Table.ReplaceValue(
- 向下填充,
- null,
- "",
- Replacer.ReplaceValue,
- {"列1", "列2", "列3", "列4", "列5", "列6"}
- ),
- 自定义1 = Table.Group(
- 替换的值,
- "列1",
- {
- "data",
- each [
- a = List.Select(Record.ToList(Table.First(_)), (x) => x <> ""){1},
- b = List.Accumulate(
- {"主料", "辅料"},
- {},
- (m, n) =>
- m
- & {
- Text.Combine(
- List.Transform(
- List.Split(
- List.Select(
- List.Combine(
- List.Skip(
- Table.ToColumns(Table.SelectRows(Table.Skip(_), each [列2] = n)),
- 2
- )
- ),
- (y) => y <> ""
- ),
- 2
- ),
- (x) => Text.Combine(x)
- ),
- ","
- )
- }
- ),
- c = Table.FromRows({{a} & b}, {"菜名", "主料", "辅料"})
- ][c]
- },
- 0,
- (x, y) => Number.From(Text.Contains(y, "名称"))
- ),
- 删除的列 = Table.RemoveColumns(自定义1, {"列1"}),
- #"展开的“data”" = Table.ExpandTableColumn(删除的列, "data", {"菜名", "主料", "辅料"}, {"菜名", "主料", "辅料"})
- in
- #"展开的“data”"
复制代码
|