September 29, 2020
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.
July 16, 2010
Welcome to our forum! The reason you're having trouble is because these analysis type tasks shouldn't be done in Power Query. Power Query is for getting and cleaning the data ready for analysis in Excel or Power Pivot.
In Sheet2 of the attached file I've loaded your data into Power Pivot via Power Query, then created PivotTables for the sum of products dispatched by customer and the distinct count of products purchased by customer to give you an idea of how Power Pivot PivotTables can work for you.
I hope that points you in the right direction. If you'd like to learn how to use Power Pivot, please consider my Power Pivot course.