Power Query (Dataflow) Sum a Column from a Different Table

[etane]

Hello.

Is there an easy way to sum a field that sits in a different table using Power Query (Dataflow)?  Below is an example.  I have a Sales Table that tracks transactions by customer.  And, I have a Product Sales Table that provides the same transactions but with line level detail such as SKU, QTY and Extended Price.  I want to be able to add a field in the Sales Table that captures total number of Engines per transaction by summing the QTY field in Product Sales Table.

I am able to achieve this by creating a conditional column (sum if product is Engine) and group by transaction # in the Product Sales Table then merge with the Sales Table expanding on the conditional column.  

However, is there a way to do the same thing with just a custom column?  I am not good with M code so hopefully it could be done in calculated column.  

Thanks.

[v-veshwara-msft]

Hi @etane ,
Thanks for posting in Microsoft Fabric Community,
Since you prefer to avoid M code, you can achieve this using a calculated column using DAX instead of Power Query.

To calculate the total number of engines per transaction, follow these steps:

1. Confirm that there is a relationship between the Sales and Product Sales tables on the Transaction # field. [Product Sales----->Sales (Many to One)].

2. In the Sales Table, create a calculated column using the following DAX formula:

   EngineQty =
   VAR TransactionID = Sales[Transaction #]
   RETURN
   CALCULATE(
       SUM('Product Sales'[QTY]),
       'Product Sales'[Transaction #] = TransactionID,
       'Product Sales'[Part Type] = "Engine"
   )
   

3. This formula filters the Product Sales Table for rows matching the transaction number and where the Part Type is "Engine", then sums the QTY values.

4. After applying this calculated column, Sales Table will now display the total quantity of engines per transaction like below.

   

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best Regards,
Vinay.

[etane · Accepted solution]

Thank you for this.  However, I need to build the data in Dataflow because I have to append this table with another table for a combined sales history table

At the moment, I am working with someone in our IT department to do the table building via SQL ands storing it in a local server for me to pull the data.

[v-veshwara-msft]

Hi @etane ,

Thanks for getting back.

You can achieve this in Dataflow using a custom column. 

Since you mentioned working with your IT team to build the table using SQL and store it in a local server, this could be another viable approach.

Hope this helps you achieve your requirement! If you have any doubts or need further assistance, please feel free to reach out.

Please consider marking the helpful reply as Accepted solution to help others find it quickly.
Best Regards,
Vinay.

[lbendlin]

This is a very expensive transform.  Don't do it. Let the data model of the semantic model consuming the dataflow do that work.  Keep your dataflows to simple straightline spool type transforms.