powerquery动太列扩展

foreversun

考前成绩：


let
    源 = Excel.Workbook(File.Contents("F:\数据\考试成绩合表\考试成绩.xlsx"), null, true),
    考后成绩_Sheet = 源{[Item="考后成绩",Kind="Sheet"]}[Data],
    提升的标题 = Table.PromoteHeaders(考后成绩_Sheet, [PromoteAllScalars=true]),
    更改的类型 = Table.TransformColumnTypes(提升的标题,{{"姓名", type text}, {"班级", Int64.Type}, {"考室", Int64.Type}, {"考号", type text}, {"性别", type text}, {"身份证", type text}, {"学籍号", type text}, {"序号", Int64.Type}})
in
    更改的类型

考后成绩：

let
    源 = Excel.Workbook(File.Contents("F:\数据\考试成绩合表\考试成绩.xlsx"), null, true),
    考前成绩_Sheet = 源{[Item="考前成绩",Kind="Sheet"]}[Data],
    提升的标题 = Table.PromoteHeaders(考前成绩_Sheet, [PromoteAllScalars=true]),
    更改的类型 = Table.TransformColumnTypes(提升的标题,{{"姓名", type text}, {"班级", Int64.Type}, {"考室", Int64.Type}, {"考号", type text}, {"册", Int64.Type}})
in
    更改的类型

两表合并：
let
    源 = Table.NestedJoin(考前成绩,{"考号"},考后成绩,{"考号"},"n"),
    #"展开的“n”" = Table.ExpandTableColumn(源, "n", {"姓名", "班级", "考室", "考号", "性别", "身份证", "学籍号", "序号", "xx", "yy"}, {"n.姓名", "n.班级", "n.考室", "n.考号", "n.性别", "n.身份证", "n.学籍号", "n.序号", "n.xx", "n.yy"})
in
    #"展开的“n”"
问题：
    #"展开的“n”" = Table.ExpandTableColumn(源, "n", {"姓名", "班级", "考室", "考号", "性别", "身份证", "学籍号", "序号", "xx", "yy"}, {"n.姓名", "n.班级", "n.考室", "n.考号", "n.性别", "n.身份证", "n.学籍号", "n.序号", "n.xx", "n.yy"}) 这里有没有办法改成自动扩展，有多少列就扩展多少列。

dambcer

可以的……………………

foreversun

dambcer 发表于 2024-12-6 17:21
可以的……………………

如何写的，请提示下呢。列名相同，如何处理呢。

dambcer

1. #"展开的“n”" = Table.ExpandTableColumn(源, "n", Table.ColumnNames(考后成绩), List.Transform(Table.ColumnNames(考后成绩),each "n."& _))

复制代码

foreversun

dambcer 发表于 2024-12-6 17:46

解决， 谢谢

pxy0409

dambcer 发表于 2024-12-6 17:46

学习了!实务中暂时没有碰到列数会增加的情况,都是一次展开.