Table.TransformColumnNames in nested table

Hi ooptennoort ,

Ok, so there's two stages to this. The first is to perform the dynamic column name changes, the second is to then apply that to a nested table.

So, to do the dynamic change, you would use something like this:

= Table.RenameColumns(
    prevStep,
        List.Zip(
            {
                Table.ColumnNames(prevStep),
                List.Transform(Table.ColumnNames(prevStep), each Text.Replace(_, ":", ""))
            }
        )
    )

Now, to apply that to the nested tables, we need to wrap the whole lot in a Table.TransformColumns and change relevant references to the previous step ('prevStep') to the access operator ('_'):

Table.TransformColumns(
    prevStep,
    {
        "nestedTableColumnName",
        each Table.RenameColumns(
            _,
            List.Zip(
                {
                    Table.ColumnNames(_),
                    List.Transform(Table.ColumnNames(_), each Text.Replace(_, ":", ""))
                }
            )
        )
    }
)

Full example query to turn this:

...into this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSouKU1LU4rVgfBKMjLz0ovh3MyS1FwIzwjIy80vSlVAqIcLIWmCi0F1xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column_Name = _t]),
    groupRows = Table.Group(Source, {"ID"}, {{"data", each _, type table [ID=nullable text, Column_Name=nullable text]}}),
    renameNestedColumns = Table.TransformColumns(
    groupRows,
    {
        "data",
        each Table.RenameColumns(
            _,
            List.Zip(
                {
                    Table.ColumnNames(_),
                    List.Transform(Table.ColumnNames(_), each Text.Replace(_, "_", "____"))
                }
            )
        )
    }
),
    expandNestedData = Table.ExpandTableColumn(renameNestedColumns, "data", {"Column____Name"}, {"Column____Name"})
in
    expandNestedData

Pete