各位老师，表格转换请教（PQ方面）

sayhi95

1. 。。。

复制代码

dskarmed

Table.Combine(List.Transform(Table.Split(Excel.CurrentWorkbook(){[Name="Sheet1__FilterDatabase"]}[Content],4),each Table.PromoteHeaders(Table.Transpose(_))))

howfun2002

let
    Source = Excel.CurrentWorkbook(){[Name="表3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"列1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "列1", Splitter.SplitTextByDelimiter("：", QuoteStyle.Csv), {"列1.1", "列1.2"}),
    拆分表格 = Table.Split(#"Split Column by Delimiter",4),
    Custom1 = List.Transform(拆分表格, each  Table.Transpose(_)),
    Custom2 = Table.Combine(Custom1),
    #"Promoted Headers" = Table.PromoteHeaders(Custom2, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"姓名", type text}, {"班级", type text}, {"录取学校", type text}, {"毕业小学", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([姓名] <> "姓名"))
in
    #"Filtered Rows"

limonet

关键字：Table.Pivot

limonet

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    已添加索引 = Table.AddIndexColumn(源, "索引", 1, 0),
    已透视列 = Table.Pivot(已添加索引, List.Distinct(已添加索引[属性]), "属性", "值", each _),
    转换 = Table.FromColumns(List.Skip(Table.ToRows(已透视列){0}),{"姓名","班级","录取学校","毕业小学"})
in
    转换

sart

List解法
let
    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    Split = Table.SplitColumn(Source, "列1", Splitter.SplitTextByDelimiter("："), {"a", "b"}),
    Custom1 = Table.ToColumns(Split),
    Custom2 = List.Split(Custom1{1},4),
    Custom3 = Table.FromRows(Custom2,List.FirstN(Split[a],4))
in
    Custom3