使用Power Query动态重命名一组列

Question

我正在尝试在Power Query中动态重命名一组列，List1是原始列名，List2是新列名。我想我需要将 List1 和 List2 合并为一个对列表，但无法找出正确的语法。

非常感谢！

    let
    //list of original column names 
    List1= {"Name1","Name2","Name3","Name4"},
    //Create test table
    Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),
    //list of new column names
    List2 = {"NewName 1","NewName 2","NewName 3","NewName 4"},
    //Rename columns (in practice, the two lists of names will be dynamic, not hard coded as below)
    Result = Table.RenameColumns(Source, {
        {"Name1","NewName 1"},
        {"Name2","NewName 2"},
        {"Name3","NewName 3"},
        {"Name4", "NewName 4"}})
    in
        Result

Answer 1

如果你有一个包含新旧名称的表，那么你可以使用以下模式

let
    rename_list = Table.ToColumns(Table.Transpose(Table2)),
    result = Table.RenameColumns(Table1, rename_list, MissingField.Ignore)
in result

其中表2是"重命名表"，表1是包含数据的初始表。

这里详细描述了这个想法https://bondarenkoivan.wordpress.com/2015/04/17/dynamic-table-headers-in-power-query-sap-bydesign-odata/

Answer 2

如果您有所需的结果列名，似乎可以将Source转换回行，然后在List2上调用Table.FromRows

let
    //list of original column names 
    List1= {"Name1","Name2","Name3","Name4"},
    //Create test table
    Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),
    //list of new column names
    List2 = {"NewName 1","NewName 2","NewName 3","NewName 4"},
    Result = Table.FromRows(Table.ToRows(Source), List2)
in
    Result

（除非假设是错误的，例如 Name 2将始终是第二列。

Answer 3

根据伊万的解决方案陈述原始问题，在这里。Carl's 具有相同的结果，并且对于我给出的示例来说稍微简单一些，但是，我的情况将受益于在表中明确列出重命名对（即。表2）。 另外，将 MissingField.Ignore 参数与 Table.RenameColumns 一起使用意味着它只会更改我要在生产查询中重命名的列的选择，其余的将保持不变。

let
    //list of original column names 
    List1= {"Name1","Name2","Name3","Name4"},
    //Create test table
    Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),
    //list of new column names
    List2 = {"NewName 1","NewName 2","NewName 3","NewName 4"},
    //Rename columns (in practice, the two lists of names will be dynamic, not hard coded as below)
    //Bring List1 and List2 together as rows in a table
    Table2 = Table.FromRows({List1,List2}),
    //Create a list of rename pairs 
    RenameList = Table.ToColumns(Table2),
    //Call to Table.RenameColumns 
    Result = Table.RenameColumns(Source, RenameList, MissingField.Ignore)
in
    Result

Answer 4

终于...使用以下函数弄清楚了

Table.TransformColumnNames（table as table， nameGenerator as function， optionaloptions as nullable record） as table

首先创建一个nameGenerator函数（例如MyFuncRenameColumns），以提供一个新的列名，给定任何原始列名作为输入。

在我的示例中，这是我为 MyFuncRenameColumns 编写的代码：

    let
        MyFunctionSwitchColumnName = (originalColumnName) as text =>
        let
            //list of original column names 
            List1= {"Name1","Name2","Name3","Name4"},
            //Create table
            Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),
            //list of new column names
            List2 = {"NewName 1","NewName 2","NewName 3","NewName 4"},
            //Create table matching List1 to corresponding new value in List2
            CreateRecord = Record.FromList(List2,List1),
            ConvertedtoTable = Record.ToTable(CreateRecord),
            //Filter table to just the row where the input originalColumnName matches 
            ReduceExcess = Table.SelectRows(ConvertedtoTable, each [Name] = originalColumnName),
            //Return the matching result in the [Value] column (or give the original column name if there was no valid match)
            NewColumnName = try ReduceExcess{0}[Value] otherwise originalColumnName
        in 
            NewColumnName
    in
        MyFunctionSwitchColumnName

下面是将其用作 Table.TransformColumnNames 的参数之一的位置：

    let
        //list of original column names 
        List1= {"Name1","Name2","Name3","Name4"},
        //Create table
        Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),
        RenameColumns = Table.TransformColumnNames(Source, MyFuncRenameColumns)
    in
        RenameColumns

希望对某人有所帮助！