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

[dumpguy]

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 to how to accomplish this.
Basically, it's about whether the individual Table.FillUp actions (in the nested tables) can be performed all at once, so not one after the other as in my code, each time creating a new column and deleting the previous one.
It would also be great if the Table.FillUp in the columns [1000], [1500], [2000] or [7000] is only executed if the respective column actually exists. So with embedded IF...THEN.
Thanks in advance for your support.

GroupRows_and_FillUp_Col_1000 = Table.Group(

RemovedColumns,
{"Index of iPPE Group"},
{{"iPPE grouped 1", each Table.FillUp(_, {"1000"}), type table}}
),
RemovedOtherColumns = Table.SelectColumns(GroupRows_and_FillUp_Col_1000, {"iPPE grouped 1"}),

FillUp_Col_1500 = Table.AddColumn(
RemovedOtherColumns,
"iPPE grouped 2",
each Table.FillUp([iPPE grouped 1], {"1500"})
),
RemovedOtherColumns1 = Table.SelectColumns(FillUp_Col_1500, {"iPPE grouped 2"}),

FillUp_Col_2000 = Table.AddColumn(
RemovedOtherColumns1,
"iPPE grouped 3",
each Table.FillUp([iPPE grouped 2], {"2000"})
),
RemovedOtherColumns2 = Table.SelectColumns(FillUp_Col_2000, {"iPPE grouped 3"}),

FillUp_Col_7000 = Table.AddColumn(
RemovedOtherColumns2,
"iPPE grouped 4",
each Table.FillUp([iPPE grouped 3], {"7000"})
),
RemovedOtherColumns3 = Table.SelectColumns(FillUp_Col_7000, {"iPPE grouped 4"}),

#"Keep only first row of nested tables" = Table.AddColumn(
RemovedOtherColumns3,
"First row only",
each Table.FirstN(Record.Field(_, List.Last(Table.ColumnNames(RemovedOtherColumns3))), 1)
),
RemovedOtherColumns4 = Table.SelectColumns(#"Keep only first row of nested tables", {"First row only"}),

Expand_all = Table.Combine(#"RemovedOtherColumns4"[First row only])
in
Expand_all

Greetings,
Stefan

[Anonymous]

Actually, you don't even need to do that! Table.FillDown uses a list as it's parameter, so if you know which columns you want, you can use make a query with all the possible columns, another query with Table.ColumnNames, and then do

Table.FillUp(TableOrPriorStep, List.Intersect({NameOfListOfPossibleColumns, NameOfTableColumnNames}))

--Nate

--Nate

[Anonymous]

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

[dumpguy]

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.

[dumpguy]

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: