none
Replace Headings RRS feed

  • Question

  • When cleaning up data, I often find myself changing the values of all of the headings in my source table. I know I can use a simple M expression such as

        Rename.Headers = Table.RenameColumns(Source,{{"reg", "Region"}, {"div", "Division"}})

    I want to be more dynamic, holding all of the old and new values in an Excel table, read that table, and apply the values. I can deploy such code regularly, and being an Excel table I have a simple maintenance option.

    I thought that by getting that table into a list with each row of the list as a comma separated pair, such as  reg,Region and div,Division, I could then pass that list toTable.RenameColumns().

    I can get the list simply enough with

        List.Headings = Table.ToList(Excel.CurrentWorkbook(){[Name="Headings"]}[Content]),

    but it fails when I try to apply this to the data table

        Source = Excel.CurrentWorkbook() {[Name="SalesData"]}[Content],
    //    Rename.Headers = Table.RenameColumns(Source,{{"reg", "Region"}, {"div", "Division"}})
        Rename.Headers = Table.RenameColumns(Source, List.Headings)

    The last line errors saying that the expression expects a RenameOperations value.

    Any ideas how to achieve my objective?

    Thursday, March 12, 2015 10:11 AM

Answers

  • A single comma-separated pair won't work; you need lists with two values each. If your table has two columns with the old name and new name, then you can use Table.ToRows to convert it to a list of lists.
    Thursday, March 12, 2015 1:30 PM

All replies

  • A single comma-separated pair won't work; you need lists with two values each. If your table has two columns with the old name and new name, then you can use Table.ToRows to convert it to a list of lists.
    Thursday, March 12, 2015 1:30 PM
  • I originally tried to create a list of lists, and I thought I had tried Table.ToRows, but maybe not as that is working fine now.

    Thanks.

    Thursday, March 12, 2015 2:53 PM