cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dumpguy
Frequent Visitor

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 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

4 REPLIES 4
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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.

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:

dumpguy_0-1651149840485.png

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

dumpguy_1-1651150018920.png

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors