Forum Discussion

ooptennoort's avatar
ooptennoort
Advocate I
3 years ago
Solved

Table.TransformColumnNames in nested table

I need to replace ":" with "" in column names but in a nested table (that I cannot expand yet)... 2 problems:

1) How do I get a nested table as table (1st argument of Table.TransformColumnNames)
2) Then how do i actually get the column names' text replaced

I tried along these lines:

 

= Table.TransformColumnNames( Table.Column(AddRemovedTopRowHeaderPromoted,"Ranges"), Text.Replace(_,":",""))

 

and

 

= Table.TransformColumnNames( Table.FromList(RemovedTopRowPromotedHeaders[Data], Splitter.SplitByNothing(), null, null, ExtraValues.Error), (_) as text => Text.Clean(Text.Replace(_,":","")))

 

and


RecordsToList = Table.AddColumn(#"Removed Other Columns1", "RecordsToList", each Record.ToList([Data]{1})),
RenRecordsToList = Table.AddColumn(RecordsToList, "RenRecordsToList", each List.ReplaceValue([RecordsToList],":","", Replacer.ReplaceText)),
Custom2 = Table.RenameColumns([Data], [RecordsToList],[RenRecordsToList] ),
AddRemTopRowHeaderPromoted = Table.AddColumn(Custom2, "Ranges", each Table.PromoteHeaders( Table.Skip([Data],1))),

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

2 Replies

  • ooptennoort's avatar
    ooptennoort
    Advocate I
    3 years ago

    Wow, thank you!!! List.Zip! Will need to study closer how you did it! Thx

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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