Conditionally Summing Sales from another query

Power Query
1

Hi

I’m having trouble with the M code for creating a new column in Power Query Editor.

I have a query called ‘Call Outcomes’.

I want to create a column in ‘Call Outcomes’ called ‘Total Sales’ that sums the sales for the customer that are in the ‘Sales’ query.

However, I only want the total sales for each customer based on the following criteria:

  1. ‘Converted’ = Yes
  2. If Converted = Yes, then sum sales only from the ‘Converted Date’

For example:

  • for Customer ID = 407627, Converted = No so Total Sales will be blank/zero
  • for Customer ID = 386535, Converted = Yes and Converted Date = 01/12/2022 so Total Sales will be those sales made on or after 01/12/2022 which in this case is $40

I’m trying to achieve this calculation in Power Query because the actual Sales table is large and I’d prefer not to import it into Power BI.

Any guidance would be much appreciated.

Thanks

Sample File.pbix (111.8 KB)

2

Could you do this at the data source?

If not, how about turning that Sales table into an aggregated table?

  1. Group Sales on Customer ID, select “All Rows” (call new column “Sales”)
  2. Merge Sales with Call Outcomes (from Sales)
  3. Expand the fields “Converted” and “Converted Date”
  4. Filter rows where [Converted] = “Yes”
  5. Only transform remaining rows…
    Table.ReplaceValue( 
        PrevStepName, 
        each [Sales], 
        each try 
            List.Sum( Table.SelectRows( [Sales], (x)=> x[Sale Sold Date] >= [Converted Date] )[Sale Line Total] ) 
            otherwise 0,
        Replacer.ReplaceValue,
        {"Sales"}
    )

.
Doing it this way around, should increase performance and Sales is much smaller to load. See Sales (2) query

BTW you didn’t include the data
Sample File.pbix (106.0 KB)
Call Outcomes.csv (122.3 KB)
Sales.csv (499.7 KB)

Hope this helps.

3

Hi @Melissa

Thank you for staring me in the right direction.

I don’t have access to the source data. I tried the aggregated table solution but it was quite slow.

I then tried manipulating the source data in Power Query and solved the issue using Group By with multiple conditions and then merge queries with multiple conditions.

I should have thought about this earlier but my headspace was elsewhere.

Again, thanks for your help.