Forum Discussion

MagikJukas's avatar
MagikJukas
Resolver III
3 years ago

Max value from each table

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    
Need Help
MagikJukas's avatar
MagikJukas
Resolver III
3 years ago

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's avatar
    collinsg
    Solution Sage
    3 years ago

    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.