给定你的源CSV（格式相当糟糕）：

name;number;list
"name1";1234;"1. item1
2. item2
3. item3
";"name2";4567;"1. item4
2. item5
3. item6
"

字符串
在Power Query中打开时，如果将“”放在“”上，则会导致=>

的数据
阅读代码注解并检查每个Applied Step，了解将其转换为图片结果所涉及的步骤：

let
    Source = Csv.Document(File.Contents("C:\Users\ron\Desktop\New Text Document.csv"),
        [Delimiter=";", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"name", type text}, {"number", Int64.Type}, {"list", type text}}),
    
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", 
        each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

//Replace blanks in list with "name", then delete "name"
    #"Replace Blank list items" = Table.ReplaceValue(
        #"Removed Blank Rows",
        each [list],
        each [name],
        (x,y,z)=> if x = "" or x = null then z else x,
        {"list"}),

//Remove items and trim "name" column
    #"Remove list items" = Table.ReplaceValue(
        #"Replace Blank list items",
        each [name],
        each [list],
        (x,y,z)=> if y = z then null else x,
        {"name"}),

    #"Quote name" = Table.TransformColumns(#"Remove list items",{
        {"name", each """" & Text.Trim(_,{";",""""}) & """", type text}}),

    #"Remove Chars from list" = Table.TransformColumns(#"Quote name",{
        {"list", each Text.TrimStart(_,{"0".."9","."," "}), type text}})
in
    #"Remove Chars from list"

型

的

您可以创建一个this并应用一些设置，使您的输入看起来像this。
在选择了该选项卡的情况下，转到Design tab。
关闭subtotals和row and column totals，然后应用Tabular Layout：
希望这有帮助！

这个在powerquery中怎么样？你可以粘贴到家庭...高级编辑器....然后用文件把它放回excel中...关闭并加载...

let Source = Csv.Document(File.Contents("C:\Temp\a.csv")),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each try Text.Repeat(";",2-(List.Count(Text.Split([Column1],";"))-1)) & [Column1] otherwise 
    if Text.Start([Column1],1)=";" then Text.AfterDelimiter([Column1],";") else [Column1]),
NewColumns=List.Transform({1 .. List.Max(List.Transform( #"Added Custom"[Custom], each List.Count(Text.Split(_,";"))))}, each "combined_"& Text.From(_)),
split = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), NewColumns),
#"Promoted Headers" = Table.PromoteHeaders(split, [PromoteAllScalars=true])
in  #"Promoted Headers"

字符串

的数据
如果需要，右键单击删除第一列
如果需要，右键单击并填写名称和编号列

从列表列中删除数字的替代版本

let Source = Csv.Document(File.Contents("C:\Temp\a.csv")),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each try Text.Repeat(";",2-(List.Count(Text.Split([Column1],";"))-1)) & [Column1] otherwise 
    if Text.Start([Column1],1)=";" then Text.AfterDelimiter([Column1],";") else [Column1]),
NewColumns=List.Transform({1 .. List.Max(List.Transform( #"Added Custom"[Custom], each List.Count(Text.Split(_,";"))))}, each "combined_"& Text.From(_)),
split = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), NewColumns),
#"Promoted Headers" = Table.PromoteHeaders(split, [PromoteAllScalars=true]),
#"Remove End" = Table.TransformColumns(#"Promoted Headers",{{"list", each Text.AfterDelimiter(_,". "), type text}})
in  #"Remove End"

型

的
整个事情是硬编码的3列与2部分的代码