COUNTIFS in Power Query|Power Query|Excel Forum|My Online Training Hub

Hi All,

I have a report provided to me that contains the information contained in columns A through E, generic customer and product ordering and order status information.

I am looking to use this data to create a Dashboard however need to understand some additional information such as the # unique products each customer has bought and then the total quantity of products a customer has bought - both subject to being despatched and not cancelled.

I can do this in Excel using a SUMIFS and COUNTIFS as per the attached, however the issue being that when the report refreshes is wipes out my additional formulas in F and G (Could be a real simple fix here?)

I am therefore looking to recreate the report using the connection string etc (successfully) and then look to add a couple of Custom Columns all done via PowerQuery, I am hitting a brick wall though as to how I can re-create the SUMIFS and COUNTIFS functionality in Columns F&G within PowerQuery.

I would appreciate all advice offered on this, the aim ultimately being able to have a single report to refresh that would update all associated charts etc into the Dashboard without needing to reference multiple tables etc.

Thanks for taking the time to read this.