Forum Discussion
PowerQuery syntax for a column in a table in a cell
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.
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 _))*100Output:
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.
2 Replies
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))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 _))*100Output:
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.