PowerQuery syntax for a column in a table in a cell

[agtech]

Hi,

Wonder if anyone knows what is the syntax for referring to a List in a Table in a cell of a PowerQuery table.

I just want to find the maximum value of the 'Net Sales' coulmn of the tables that are the cells of the "Count" column. I thought it would be [Count][Net Sales] as TableColumn works in other instances but not in this instance.

[Eyelyn9 · Accepted solution]

Hi @agtech ,

I have created a data sample:

According to my understanding, you want to get the max score *100 of each group, right?

Please try:

=List.Max(
      List.Transform(Table.SelectRows(#"Changed Type", (q) => q[Type] = [Type])[Net Sales] ,each _))*100

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33]

Hi @agtech 

You need to add the Column Score into the table (let's call it inside table) in Column Count (let's call it Outside Table ), right? Yes, it is  [Count][Net Sales] , but when you are adding a column in the inside table, there is no Count column as it is in the outside table...so modify your code like this

= Table.AddColumn(#"Grouped Rows", "Store Score", (ot) => Table.AddColumn(ot[Count],"Score", each 
Number.Round([Net Sales] / List.Max( ot[Count][Value]),2)*100))