PowerQuery Many 2 Many Relationships

[rabbit_post]

Hi All

I have just started to use PowerQuery and my first real world problem involves a many to many relationship. I have successfully merged the tables together to achieve the correct pivot table results but I am looking to avoid this step. I am looking to show the quantity of each child item required each month as a consequence of the demand for the parent items and the parent / child relationships. With relation to the attached file and screen shots I have created relationships as below using a unique list of items in Item Master to provide a 1 to Many relationship with the two tables.

Capture1.JPG

However, the resulting pivot table does not display the correct quantities, see below. The quantity for each child repeats each month.

Capture2.JPG

I would be grateful if someone could steer me the right direction.

Thanks


Also posted on https://stackoverflow.com/questions/...-relationsship

[sandy666]

Attach a sample workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Note that I didn't say I'll help, I said attach example excel file