Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.