代码之家 › 专栏 › 技术社区 › JP_CPT

转置到多列换行n

vba powerquery excel

JP_CPT · 技术社区 · 6 年前

例如:

Center  Course  Type    Student_Num Student_Name
1001    Science 1   1   John
1001    Science 1   2   Linda
1001    Science 1   3   Pete
1001    Science 2   1   Susan
1001    Science 2   2   Gary
1001    English 1   1   Bob
1001    English 1   2   Kate
1002    Science 1   1   Alice
1002    Science 1   2   Rick
1002    Science 1   3   Pat
1002    Science 1   4   Shanon
1002    Science 1   5   Louis
1002    English 1   1   Cathleen

转化:

Center  Course  Type    Student1_Num    Student1_Name   Student2_Num    Student2_Name
1001    Science 1   1   John    2   Linda
1001    Science 1   3   Pete        
1001    Science 2   1   Susan   2   Gary
1001    English 1   1   Bob 2   Kate
1002    Science 1   1   Alice   2   Rick
1002    Science 1   3   Pat 4   Sharon
1002    Science 1   5   Louis       
1002    English 1   1   Cathleen

谢谢

1 回复 | 直到 5 年前

MarcelBeug 6 年前

具有参数、列表和查询的解决方案。

它的性能相当好:

我在Excel中测试了100000条记录,大约花了15秒;

大约2:15分钟内录制了500000条记录。

参数MaxStudentsPerRow:

2 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]

列表扩展列表

“0”,“Student1\u Num”,“Student1\u Name”

“1”,“Student2\u Num”,“Student2\u Name”

let
    Source = {1..MaxStudentsPerRow},
    ToText = List.Transform(Source, each {Text.From(_-1), "Student"&Text.From(_)}),
    AddedNumAndName = List.Transform(ToText,each {_{0},_{1}&"_Num",_{1}&"_Name"})
in
    AddedNumAndName

查询:

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Center", "Course", "Type"}, {{"AllData", each Table.AddIndexColumn(_,"Index",0,1)}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Student_Num", "Student_Name", "Index"}, {"Student_Num", "Student_Name", "Index"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded AllData", "Index", "Index - Copy"),
    #"Calculated Modulo" = Table.TransformColumns(#"Duplicated Column", {{"Index", each Number.Mod(_, MaxStudentsPerRow), type number}}),
    #"Integer-Divided Column" = Table.TransformColumns(#"Calculated Modulo", {{"Index - Copy", each Number.IntegerDivide(_, MaxStudentsPerRow), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Integer-Divided Column", "NumAndName", each Record.FromList({[Student_Num],[Student_Name]},{"Num","Name"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Student_Num", "Student_Name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Index", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Index]), "Index", "NumAndName"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index - Copy"}),
    Expanded = List.Accumulate(ExpandList,#"Removed Columns1",(t,e) => Table.ExpandRecordColumn(t,e{0},{"Num","Name"},{e{1},e{2}}))
in
    Expanded

推荐文章