Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to simplify M code for multiple actions, each on particular columns within nested tables

Hi there, I have working M-code, but it looks to me like it could be simplified significantly. Since I'm not exactly a master of M-code syntax, I'd appreciate it if someone could give me a clue as...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

I'm sure that you could use Table.TransformColumns instead of Add Column, which would give you a single step for filling up, and eliminate the need for the Remove Column steps. You can also add the MissingField.Ignore parameter as the optional parameter in Table.TransformColumns, so that if any particular columns are not present, they will be ignored by the M script.

 

--Nate

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Nate,
    tried to figure it out by Table.TransformColumns, but unfortunatly I´m not capable to find the right syntax to get the nested tables transformed then by Table.FillUp.

     

    Possibly a screenshot helps to understand. This is the initial situation:

    This is how it looks like after one of the FillUp steps:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,
    sorry for the late reply, but my solution back then was 

     

    FillDown_columns_in_nested_tables = Table.Group(
    SortedRows,
    {"Node", "Variant"},
    {{"Grouped", each Table.FillDown(_, {"Col ABC", "Col 123", "Col XYZ"}), type table}}
    ),

    RemovedOtherColumns = Table.SelectColumns(
    FillDown_columns_in_nested_tables,
    {"Grouped"}
    ),
    Expand_dyn = Table.ExpandTableColumn(
    RemovedOtherColumns,
    "Grouped",
    Table.ColumnNames(RemovedOtherColumns[Grouped]{0}),
    Table.ColumnNames(RemovedOtherColumns[Grouped]{0})
    ),

     

    At least this shortened all a bit and of course - as you suggested - a NameOfListOfPossibleColumns could be used instead of writing each single column name. Perhaps I once will try to get it done be Table.TransformColumns.

    Thank you.