假设您从具有三个列且没有列标题的 Table1 范围内的源数据开始,此代码将在powerquery中工作(粘贴到Home ... Advanced Editor ...中)
它使用split函数创建两个自定义列,以拆分成一个列表
然后在扩展之前将两个列表合并到一个表中
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],#"Added Custom" = Table.AddColumn(Source,"Custom",each Text.Split([Column2],":")),#"Added Custom1" = Table.AddColumn(#"Added Custom","Custom.1",each Text.Split([Column3],#"Added Custom2" = Table.AddColumn(#"Added Custom1","Custom.2",each Table.FromColumns({[Custom],[Custom.1]})),#"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom2",{"Column1","Column2"},{"Column1.1","Column2.1"}),#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.2",{"Column2","Column3","Custom.1"})
in #"Removed Columns"
或者,以下内容可同时处理任意数量的列
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],#"Changed Type" = Table.TransformColumnTypes (Source,List.Transform(Table.ColumnNames(Source),each {_,type text})),TableTransform = Table.Combine(List.Transform(List.Transform(Table.ToRecords(#"Changed Type"),(x) => List.Transform(Record.ToList(x),each Text.Split(_,":"))),each Table.FromColumns(_,Table.ColumnNames(#"Changed Type")))),#"Filled Down" = Table.FillDown(TableTransform,{"Column1"})
in #"Filled Down"
本文链接:https://www.f2er.com/1975110.html