Dataverse, Count Rows of lookup table column based on condition within lookup column

[ilauzirika]

Hi! It feels like I am trying to do something very wrong as I cannot seem to find a good answer online. Is there a solution to the custom column that I am trying to build?

I am getting my data from Dataverse (generated by power apps), one of the tables that I am importing is customers. One of the columns in Customers is a lookup to another table called orders (with many orders possible). In Power Query you can see  a Table under the [Orders] column in the Customer Table. When you click on any of the Table values in any of the rows of the Customers Table you can see all the orders linked to that customers. One of the columns within [Orders] is "ProductType". 

What I want to do is, in the Customers Table create a custom column called "Potato" that counts the number of rows within the [Orders] table that match the Potato condition for their ProductType for each Customers row. It seems so straightforward to me, but I cannot find the right formula to do it, nor I can find some sample code that I can try to learn and understand. 

I will try to visualize the formula here:

Create a new custom column in Customers table  = (Table.RowCount(Table.SelectRows([Orders], [Orders]ProductType = "Potato"))

This is how my Orders column looks like inside the Customers Table. For each of those tables I would like to count how many times an order for a Potato comes up and attach the count to a custom column after the Orders column.

What would be the right approach for this?

Thank you for the help!

[edhans · Accepted solution]

Your code should look like this. You are missing the each keyword in Table.SelectRows, and since Table.SelectRows is already referencing the [Orders] table you do not need to refer to it again, just to the product type within a field. You were definitely on the right path though, just need to tweak your syntax a bit.

Table.RowCount(
   Table.SelectRows(
      [Orders], 
      each [Product Type] = "Potato"
   )
)

That may not perform well depending on the data source. It might be faster to:

• expand all of those orders in another table or access the orders table directly
• Do your filter for the Potato product type
• Do a Group By then count the records
• Merge that result with your original table.

The above will fold on a SQL table and the server does all of the work. I think the DataVerse will to. The code in the box above most assuredly will not @ilauzirika 

[ilauzirika]

Thank you to both of you, I definitely took the wrong approach at this. I tried the corrected formula as indicated by edhans, just to learn what the load would be on the system. well, needless to say, the PC crashed after using over 20GB of Virtual memory hahaha.... lesson learnt. 

If I could accept both posts as a solution I would. I am using edhans solution as it works very well for my setup. But both solutions make a lot of sense and are working correctly (I tested both). 

[edhans]

Glad to help @ilauzirika - and you can accept both solutions. I went ahead and accepted @Jimmy801 's solution for you. Either way, glad you got the answer you needed.

[Jimmy801 · Accepted solution]

Hello @ilauzirika 

when I read that I really asked me if you then would need to do this for other product types and maybe other manipulations as well. If yes then I would say it would be the wrong approach. You have here basically two datatables combined as one. I would suggest in this case to load your customer-table into your datamodel and create a new table from customer id and all expanded order-table, connect them in Power BI and create measures.

Hope I was clear enough

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[edhans · Accepted solution]

Your code should look like this. You are missing the each keyword in Table.SelectRows, and since Table.SelectRows is already referencing the [Orders] table you do not need to refer to it again, just to the product type within a field. You were definitely on the right path though, just need to tweak your syntax a bit.

Table.RowCount(
   Table.SelectRows(
      [Orders], 
      each [Product Type] = "Potato"
   )
)

That may not perform well depending on the data source. It might be faster to:

• expand all of those orders in another table or access the orders table directly
• Do your filter for the Potato product type
• Do a Group By then count the records
• Merge that result with your original table.

The above will fold on a SQL table and the server does all of the work. I think the DataVerse will to. The code in the box above most assuredly will not @ilauzirika