Max value from each table

[MagikJukas]

Hello,

I would like to add a column to each table in the "Rows" column where it calculate the Max value from the Indexstart column.

thanks

[MagikJukas]

Ok, I figured that out, it was simpler than I expected.

basically, I first of all groupped my table using the "function" All rows and calculating the max of the indext start.

this gave me a list of table with another column containing the max value for each table.

I simply expanded the column containing the tables, so that I have a table with all entries including the column with the max value for each set.

[MagikJukas]

Hello @collinsg 

thanks for your comment. 

I notcied my problem description was not clear.

in the code below, you apply the Max number from the list.

#"Rows + Max Indexstart" = Table.AddColumn(
   #"Added Max Indexstart col", 
   "Copy Rows and Add Max Indexstart", 
   each Table.AddColumn(
      [Rows],
      "Max Indexstart", 
      each List.Max(#"Added Max Indexstart col"[Max Indexstart])
   )
),

Instead I want to add number from each line, to the new table.

For instance, I want the first table to contain a new column with 1, and the second table a column with 566.

your solution was pushing the max (566) both tables.

are you able to advise how to correct it? 

thanks

[collinsg]

Good day @MagikJukas ,

I think I understand now - your description was fine, my error. Here is a suggestion based on my new understanding.

I created test data

and loaded it into Power Query, grouping by Plant and Material Plant View to create a table with columns Plant, Material Plant View and Rows.

From that point I applied these steps:

1. Added a column with the max value of Indexstart for each Rows table.
2. Expanded Rows
3. Grouped by Plant and Material Plant View and using an "All Rows" aggregation. This aggregation brought the max Indexstart column into Rows.
4. Since the "All Rows" aggregation also brought Plant and Material Plant View into Rows the last step is to remove them from it.

Here is the M code, assuming  your previous step is called "Previous Step"

#"Calculate Max Indexstart" = Table.AddColumn(
   #"Previous Step", 
   "Max Indexstart", 
   each List.Max([Rows][Indexstart])),
#"Expanded Rows" = Table.ExpandTableColumn(
   #"Calculate Max Indexstart", 
   "Rows", 
   {"Owner", "Customer", "Indexstart"}, {"Owner", "Customer", "Indexstart"}),
#"Grouped Rows" = Table.Group(
   #"Expanded Rows", 
   {"Plant", "Material Plant View"}, 
   {{"Rows", each _, type table [Plant=nullable number, Material Plant View=nullable text, Owner=text, Customer=text, Indexstart=nullable number, Max Indexstart=number]}}),
#"Remove Grouping Columns" = Table.TransformColumns(
   #"Grouped Rows", 
   { {"Rows", each Table.RemoveColumns(_, {"Plant","Material Plant View"})} } )

An example of the result is

Hope this helps.

[collinsg]

Hello @MagikJukas ,

The following is an inelegant approach which creates temporary columns as stepping stones to the final result. I'm sure it could be made more elegant with some work. The code presumes your previous step is called "Previous Step". The method is

1. Add a column which is "Max Indexstart".
2. Add a column which is "Rows" with a "Max Indexstart" column added.
3. Remove the temporary columns.
4. Rename the "Copy Rows and Add Max Indexstart" column to "Rows".

#"Added Max Indexstart col" = Table.AddColumn(
   #"Previous Step", 
   "Max Indexstart", 
   each List.Max([Rows][Indexstart])
),
#"Rows + Max Indexstart" = Table.AddColumn(
   #"Added Max Indexstart col", 
   "Copy Rows and Add Max Indexstart", 
   each Table.AddColumn(
      [Rows],
      "Max Indexstart", 
      each List.Max(#"Added Max Indexstart col"[Max Indexstart])
   )
),
#"Remove staging columns" = Table.RemoveColumns(#"Rows + Max Indexstart",{"Rows", "Max Indexstart"}),
#"Rename column as Rows" = Table.RenameColumns(#"Remove staging columns",{{"Copy Rows and Add Max Indexstart", "Rows"}})

 Hope this helps and that you find a more elegant solution.