The objective is to assist a Store Manager with computing potential sales across different products and colours.  To start with let’s assume two datasets:

1. Customer-Colour dataset – a two column table which lists down the colour preference of each customer; and
2. Colour-Product-Price dataset – a three column table which lists down the multiple products associated with each colour and the associated prices

My objective is to compute the “Potential Sales” if each customer buys one unit of each colour of each product.  While this can be resolved by using formulas (see Formula solution worksheet of the workbook), I’d like to resolve this problem with the help of the Power Query and PowerPivot tools.

The initial challenge will be to establish a relationship between the two tables because one cannot establish Many to Many relationships in a PowerPivot.  The two tables above are a perfect example of Many to Many relationships because each customer likes multiple colours and each colour is associated with multiple products.

You may also view a short video of my solution here:

• Mrod says:

Hello Ashish,

I am using SUMIF function to add a column range with 2 different criteria. The criteria is “I/T” and “EA”. I want to add the cells that are I/T and the cells that are EA, to separate the 2 different sums. The rows are 31 through 34. So the formulas I used was =SUMIF(D31:D34,”EA”,C31:C34) and =SUMIF(D31:D34,”I/T”,C31:C34). However, the totals I get are 0 for both. I don’t understand why it is not calculating. Am I missing something? Thank you in advance.

C D
1 I/T
1 I/T
1 EA

• Vishal says:

Is Power Query is an mandate to create many to many relationship? I am getting this message while creating a many to many relationship “The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values.”

• Hi,

You do not have to create any relationships. You simply have to merge the two datasets in Power Query.