|
![](https://clubstatic.excelhome.net/image/common/ico_lz.png)
楼主 |
发表于 2024-5-27 15:14
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
目前想到的改进方法,较之前的有所简化,刷新速度尚未测试![](https://clubstatic.excelhome.net/image/smiley/default/lol.gif) ![](https://clubstatic.excelhome.net/image/smiley/default/lol.gif)
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(源,{{"编号", type text}}),
复制的列 = Table.DuplicateColumn(更改的类型, "路径", "路径1"),
按分隔符拆分列 = Table.SplitColumn(复制的列, "路径1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"路径.1", "路径.2", "路径.3"}),
分组的行 = Table.Group(按分隔符拆分列, {"编号","路径"}, {{"data", each Table.Skip(Table.Transpose(Table.ExpandTableColumn(
Table.NestedJoin(Table.Skip(Table.Transpose(_),3), {"Column1"}, 编号对应表, {"编号"}, "新表", JoinKind.LeftOuter),
"新表", {"名称"}, {"层级1"})),1) }}),
#"展开的“data”" = Table.ExpandTableColumn(分组的行, "data", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
#"展开的“data”" |
|