Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

How to structure and connect 2 Power Query Tables that have many to many relationships in the 2 columns that I want to filter with?

Avatar of Jeff McClellan
Jeff McClellanFlag for United States of America asked on
Microsoft OfficeMicrosoft ExcelMicrosoft Applications
2 Comments1 Solution36 ViewsLast Modified:
I have two data tables (MinMaxTable and MasterResultsTable - attached LinkingMinMaxMasterTables.xlsx).  I want to generate 2 pivot tables that will be used to generate dynamic data for a pivot chart.  The bar chart data will come from the MasterResultsTable pivot, and the charts primary axis options will be defined from the MinMaxTable pivot for AxisMaximum, AxisMinimum, MajorGrid, MinorGrid, CrossesAt.

These tables have 2 fields in common  (Crop and Attribute)  between the two, but they have a many to many relationship between the two tables.  Additionally, the values between the two tables don't always match exactly, so I've created helper tables (CropMerge and AttributeMerge) that relate the values from each table to each other.

How do I set these up in Power Query / Power Pivot to connect and filter using shared slicers for Crop and Attribute to filter the 2 pivot tables and the pivot chart?

Here's what I would like the Pivots and Charts to look like.

Ultimately, I want to print a report that has a chart for each of the Attributes available in the filtered MasterResultsTable.
Avatar of Tom Farrar
Tom FarrarFlag of United States of America imageConsultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers