many-to-many relations and visualization cross-filtering issue

krrb4u

New Member
Joined
Mar 4, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi I need help on Power query in Excel Many to Many relationship
trying to achieve few things I have put all data in below (two sheets) and explained what I need
as slicers, axis and value. Can someone please help on this.Thanks in advance.

"Job" is same on both sheets

Need to achieve the following:

Slicers Axis Values
Order(Sheet1) batch(Sheet2) Quantity (Sheet2)
Job(Sheet1) Title(Sheet2)
Name(Sheet1)
Date(Sheet1)



Sheet1

NameJobOrderDateValue
NDF103092601-1-1
29 January 2021​
5179.58​
NDF102982193-1-1
29 January 2021​
5429.58​
NDF103102654-1-1
29 January 2021​
3617.38​
ND2765-1-1
06 March 2021​
203.87​
NDF103032710-3-1
12 February 2021​
865.68​
NDF103122711-3-1
12 February 2021​
865.68​
NDF103122712-3-1
12 February 2021​
865.68​
NDF103122713-3-1
12 February 2021​
865.68​
NDF103132819-1-1
12 March 2021​
3658.62​
NDF103132828-1-1
19 February 2021​
5429​
NDF103172859-2-1
19 March 2021​
0​
ND2021022859-6-1
19 March 2021​
150.61​
NDF103142869-1-1
12 March 2021​
3617.38​
NDF103142905-1-1
12 March 2021​
3814.76​
ND2021022905-2-1
12 March 2021​
101.51​
NDF103142935-10-1
19 March 2021​
2227.65​
NDF103142935-1-1
19 March 2021​
2227.65​
NDF103142935-11-1
19 March 2021​
2227.65​
NDF103142935-3-1
19 March 2021​
2227.65​
NDF103142935-7-1
19 March 2021​
2227.65​
NDF103142935-9-1
19 March 2021​
2227.65​
NDF103102917-1-1
25 February 2021​
481.99​
NDF103152941-1-1
19 March 2021​
2684.11​
NDF103082941-2-1
19 March 2021​
200.5​
NDF103152955-1-1
19 March 2021​
3573.71​
ND2020122955-2-1
19 March 2021​
176.26​
NDF103082957-2-1
19 March 2021​
200.5​
NDF103162978-1-1
19 March 2021​
2998.66​
ND2021032978-2-1
19 March 2021​
101.51​
ND2020042985-2-1
26 February 2021​
3.96​
ND2020112978-3-1
19 March 2021​
128.04​
NDF103183104-1-1
01 March 2021​
5429.58​
ND2021032982-1-1
19 March 2021​
101.51​
NDF103162982-2-1
19 March 2021​
3776.45​
NDF103162984-1-1
19 March 2021​
4506.67​
NDF103082984-2-1
19 March 2021​
200.5​
ND2020112993-1-1
12 March 2021​
59.42​
NDF103082993-2-1
12 March 2021​
651.68​
NDF102972999-6-1
08 March 2021​
23.68​
NDF103173011-1-1
19 March 2021​
2488.12​
NDF103173046-1-1
19 March 2021​
2550.96​
NDF103152953-1-1
05 March 2021​
2425.45​
NDF103083067-1-1
05 March 2021​
200.5​
NDF103152953-2-1
05 March 2021​
2425.45​
NDF103152954-1-1
05 March 2021​
1503.52​
NDF103183101-1-1
12 March 2021​
399.74​
NDF103083101-2-1
12 March 2021​
651.68​
NDF103082578-1-1
05 March 2021​
4069.17​
NDF103142935-6-1
05 March 2021​
2227.65​
NDF103142935-8-1
05 March 2021​
2227.65​
NDF103162972-1-1
12 March 2021​
205.66​
ND*Short2990-1-1
12 March 2021​
82.94​
NDF103093117-2-1
19 March 2021​
543.39​
NDF103152942-1-1
19 March 2021​
4010.29​
NDF103082942-2-1
19 March 2021​
200.5​
ND2812-2-1
22 March 2021​
176.26​
NDF103152812-3-1
22 March 2021​
865.68​
NDF103082812-4-1
22 March 2021​
200.5​
ND2020122816-2-1
23 March 2021​
176.26​
NDF103082816-3-1
23 March 2021​
200.5​
NDF103132816-1-1
25 March 2021​
5124.18​
NDF103132814-1-1
26 March 2021​
4420.53​
ND2815-2-1
26 March 2021​
176.26​
NDF103082815-3-1
26 March 2021​
200.5​
NDF103032815-4-1
26 March 2021​
865.68​
NDF103072553-1-1
26 March 2021​
5179.58​
NDF102901801-1-1
26 March 2021​
1924.33​
NDF102901801-2-1
26 March 2021​
1924.33​
NDF103152956-1-1
26 March 2021​
3735.95​
NDF103142936-1-1
26 March 2021​
3036.45​
NDF103152945-1-1
26 March 2021​
2786.24​
ND2021032945-2-1
26 March 2021​
101.51​
NDF103132800-1-1
31 March 2021​
3054.92​
NDF103132800-2-1
31 March 2021​
3054.46​
NDF103173007-1-1
02 April 2021​
3087.81​
NDF103123007-2-1
02 April 2021​
200.5​
NDF103173038-1-1
09 April 2021​
3596.12​
NDF103132801-1-1
21 April 2021​
3617.38​
NDF103132801-2-1
21 April 2021​
3617.38​
NDF103152936-2-1
23 April 2021​
3036.45​
ND2021013088-3-1
30 April 2021​
51.41​
ND2554-5-1
12 March 2021​
24.34​
ND2971-1-1
05 March 2021​
1553.88​
ND2709-3-1
12 February 2021​
865.68​
GE0822-3-3
02 March 2021​
758​
Ta*Short1508-2-1
13 November 2020​
126.99​
SiF102991935-1-2
13 November 2020​
2278.65​
MoF102359789-2-32
08 January 2021​
204.33​
MiF102772759-1-1
20 January 2021​
1269.87​
MiF102772760-1-1
20 January 2021​
1269.87​
MiF103122761-2-1
20 January 2021​
135.64​
MiF103172861-1-1
24 February 2021​
1283.23​
MiF103182861-5-1
03 February 2021​
50.02​
Mo3040-1-1
19 February 2021​
0​
ThF103002222-1-1
02 February 2021​
866.89​
No2021012465-11-3
19 February 2021​
17.98​
Th3089-1-1
11 March 2021​
156​
Th3036-1-1
12 March 2021​
358.02​
MiF103173086-1-1
24 March 2021​
709.48​
MiF103173085-1-1
31 March 2021​
345.89​
GE3002-1-3
31 March 2021​
26197.9​
Am1630-3-1
30 November 2020​
9123.05​
Am2021020840-1-11
15 January 2021​
651.06​
TaF102831508-1-6
13 November 2020​
1777.86​
TaF102831508-1-6
13 November 2020​
1777.86​
TaF102831508-1-6
13 November 2020​
1777.86​
Mi2020121003-1-6
13 November 2020​
2212.1​
Mi2020121003-1-6
13 November 2020​
2212.1​
MiF102541003-1-6
13 November 2020​
2212.1​
MiF102751003-1-6
13 November 2020​
2212.1​
MiF102751003-1-7
20 November 2020​
2875.73​
TaF103062112-4-1
16 December 2020​
1022.13​
PeF102962401-1-1
15 January 2021​
1158.54​
PeF103022401-1-1
15 January 2021​
1158.54​
MoF102366721-1-34
19 March 2021​
6007.55​
Am1630-2-1
30 November 2020​
27217.44​
FoF103032360-1-1
05 February 2021​
2234.24​
FoF103062495-1-1
02 March 2021​
983.5​
TuF102640498-1-43
16 November 2020​
935.28​
TuF102640498-1-43
16 November 2020​
935.28​
GrF103112671-1-1
05 March 2021​
11599.5​
TuF102630498-3-39
14 December 2020​
1843.09​
TuF102630498-3-39
14 December 2020​
1843.09​
TuF103102680-1-1
24 December 2020​
0​
TuF102640498-1-17
18 January 2021​
4676.4​
TuF102640498-1-17
18 January 2021​
4676.4​
TuF102640498-1-17
18 January 2021​
4676.4​
TuF102640498-1-17
18 January 2021​
4676.4​
TaF103062112-4-2
18 January 2021​
567.85​
TaF102972112-1-5
22 January 2021​
6132.78​
TaF102972112-1-5
22 January 2021​
6132.78​
MoF102359789-2-33
23 February 2021​
1838.97​
MoF102351856-2-1
02 March 2021​
408.66​
ThF102902273-1-3
08 February 2021​
446.91​
TuF102640498-1-18
15 February 2021​
8417.52​
Tu2021030498-10-14
15 February 2021​
99​
TuF102630498-3-15
15 February 2021​
3317.56​
TuF102640498-4-19
15 February 2021​
1117.12​
TuF102640498-4-19
15 February 2021​
1117.12​
TuF102630498-7-15
15 February 2021​
651.75​
NoF103052465-5-12
19 February 2021​
204.3​
TaF103062515-1-3
19 February 2021​
695.1​
TaF103062112-4-3
19 February 2021​
2271.4​
TaF102972112-1-6
19 February 2021​
15331.95​
TaF102972112-1-6
19 February 2021​
15331.95​
ThF103112682-1-1
25 February 2021​
1124.4​
ThF102922500-1-5
26 February 2021​
2712.68​
ThF102922500-1-5
26 February 2021​
2712.68​
NoF103052465-7-1
26 February 2021​
2431.34​
ThF102922898-1-1
26 February 2021​
953.3​
ThF103012312-1-3
01 March 2021​
1215.94​
ThF102851187-1-8
01 March 2021​
96000​
PeF102962057-1-2
02 March 2021​
13350.5​
PeF102872057-1-9
02 March 2021​
801.03​
PeF102962057-1-9
02 March 2021​
801.03​
NoF103042465-6-2
05 March 2021​
3159.45​
ThF103112847-1-1
05 March 2021​
1124.4​
NoF103052465-7-2
05 March 2021​
4290.6​
NoF103042465-9-2
05 March 2021​
404.4​
PeF102962057-1-3
08 March 2021​
5340.2​
ThF102931901-1-6
10 March 2021​
19693.35​
NoF103002313-1-5
19 March 2021​
1028.16​
NoF103002313-1-5
19 March 2021​
1028.16​
OxF103022718-1-4
12 March 2021​
8264.01​
NoF103042465-8-2
12 March 2021​
717​
OxF103022718-1-5
19 March 2021​
8264.01​
ThF103122725-1-1
15 March 2021​
2742.3​
ThF103132835-1-1
15 March 2021​
347.4​
ThF103092604-1-1
17 March 2021​
580.8​
TaF103142873-1-1
19 March 2021​
11357​
TaF103062112-4-4
19 March 2021​
2271.4​
TaF103062515-1-4
19 March 2021​
3475.5​
TaF102972112-1-7
19 March 2021​
17035.5​
TuF102640498-1-19
22 March 2021​
8417.52​
Tu2021030498-10-15
22 March 2021​
594.17​
TuF102630498-10-15
22 March 2021​
594.17​
TuF102630498-2-19
22 March 2021​
7045.02​
Tu2021020498-2-19
22 March 2021​
7045.02​
Tu2021020498-2-19
22 March 2021​
7045.02​
Tu2021010498-2-19
22 March 2021​
7045.02​
TuF102630498-2-19
22 March 2021​
7045.02​
TuF102630498-3-16
22 March 2021​
3317.56​
TuF102640498-4-20
22 March 2021​
5027.09​
TuF102640498-4-20
22 March 2021​
5027.09​
TuF102640498-4-20
22 March 2021​
5027.09​
TuF102640498-4-20
22 March 2021​
5027.09​
Tu2021020498-5-18
22 March 2021​
1589.22​
TuF102640498-5-18
22 March 2021​
1589.22​
Tu2020110498-6-16
22 March 2021​
593.6​
Tu2020120498-6-16
22 March 2021​
593.6​
TuF102640498-6-16
22 March 2021​
593.6​
TuF102630498-7-18
22 March 2021​
3910.66​
Tu2021030498-8-20
22 March 2021​
2816.55​
TuF102640498-8-20
22 March 2021​
2816.55​
Tu2021030498-9-16
22 March 2021​
1736.32​
Tu2021030498-9-16
22 March 2021​
1736.32​
ThF103018586-1-15
22 March 2021​
120273.2​
MiF103172861-2-1
24 February 2021​
1383.56​
OxF103022998-1-1
26 March 2021​
11018.68​
No2021022465-11-4
26 March 2021​
719.2​
No2021022465-11-4
26 March 2021​
719.2​
TaF103142873-1-2
30 March 2021​
11357​
Pe0733-1-1
08 March 2021​
1362.25​
Pe0733-1-2
08 March 2021​
163.47​
Pe0733-1-3
08 March 2021​
3977.77​
Th1444-1-10
05 March 2021​
179.55​
Th2075-1-8
01 March 2021​
790.86​
NDF102942058-5-1
11 January 2021​
5288.02​
NDF102942058-6-1
11 January 2021​
552​
NDF103102182-13-1
19 March 2021​
4055.5​


Sheet 2

NameStateJobbatchQuantityTitle
MiHOLDF10279F10279/0
5​
Cle
MiHOLDF10299F10299/0
11​
HOL
ThHOLDF10276F10276/0
20​
HOL
PeHOLDF10296F10296/0
50​
HOL
ThHOLDF10308F10308/0
20​
HOL
TaHOLDF10307F10307/0
300​
HOL
PeHOLDF10296F10296/0
40​
HOL
NDHOLDF10310F10310/0
200​
HOL
NDHOLDF10303F10303/0
6​
HOL
TaHOLDF10307F10307/0
300​
HOL
HyHOLDF10263F10263/0
8​
Box
OnHOLDF10256F10256/0
90​
HOL
OnHoldF10260F10260/0
19​
HOL
ThHOLDF10276F10276/0
10​
Box
HyHOLDF10316F10316/0
40​
HOL
HyHOLDF10316F10316/0
40​
HOL
HyHOLDF10316F10316/0
15​
HOL
HyHOLDF10316F10316/0
55​
HOL
HyHOLDF10311F10311/0
10​
HOL
ThHOLDF10285F10285/0
8​
Box
NDHOLDF10313F10313/0
2​
HOL
NDHOLDF10313F10313/0
1​
HOL
OnHOLDF10295F10295/0
80​
HOL
NDHOLDF10161F10161/0
5​
HOL
ThHOLDF10285F10285/0
8​
Box
NDHOLDF10313F10313/0
1​
HOL
OnHOLDF10251F10251/0
1​
HOL
ThHOLDF10285F10285/0
8​
Box
ThHOLDF10285F10285/0
8​
HOL
ThHOLDF10285F10285/0
8​
HOL
HyHOLDF10311F10311/0
10​
HOL
NDHOLDF10315F10315/0
5​
HOL
TuHOLDF10314F10314/0
10​
HOL
ThHOLDF10285F10285/0
8​
Box
NDHOLDF10314F10314/0
75​
HOL
ThHOLDF10294F10294/0
20​
HOL
ThHOLDF10312F10312/0
30​
HOL
SoHOLDF10288F10288/0
10​
HOL
ThHOLDF10315F10315/0
30​
HOL
ThHOLDF10289F10289/0
8​
HOL
OnHOLDF10295F10295/0
7​
HOL
OnHOLDF10254F10254/0
4​
HOL
OnHOLDF10254F10254/0
4​
HOL
OnHOLDF10277F10277/0
75​
HOL
ThHOLDF10292F10292/0
20​
Con
ThHOLDF10300F10300/0
30​
HOL
ThHOLDF10292F10292/0
20​
HOL
ThHOLDF10292F10292/0
20​
HOL
MiHOLDF10317F10317/0
5​
HOL
SoHOLDF10272F10272/0
10​
HOL
OnHOLDF10295F10295/0
10​
HOL
OnHOLDF10295F10295/0
10​
HOL
OnHOLDF10254F10254/0
3​
HOL
ThHOLDF10285F10285/0
8​
HOL
NDHOLDF10312F10312/0
2​
HOL
OnHOLDF10251F10251/0
30​
HOL
ThHOLDF10289F10289/0
6​
HOL
OnHOLDF10251F10251/0
60​
HOL
OnHOLDF10295F10295/0
5​
HOL
OnHOLDF10295F10295/0
2​
HOL
TuCompleteF10264F10264/0
1​
Qua
TuCompleteF10264F10264/0
1​
Qua
TuCompleteF10264F10264/0
1​
Fun
NDCompleteF10290F10290/0
29​
Box
MiCompleteF10234F10234/0
4​
Inv
OnCompleteF10277F10277/0
1​
Fun
OnCompleteF10277F10277/0
1​
Qua
OnCompleteF10277F10277/0
2​
Fun
OnCompleteF10277F10277/0
1​
Fun
OnCompleteF10277F10277/0
1​
Fun
MiCompleteF10299F10299/0
1​
Box
MiCompleteF10240F10240/0
15​
Pac
SoCompleteF10272F10272/0
1​
Qua
SoCompleteF10272F10272/0
1​
Fun
SoCompleteF10272F10272/0
0​
Qua
NDCompleteF10290F10290/0
1​
HOL
NDCompleteF10290F10290/0
1​
HOL
TuCompleteF10263F10263/0
1​
Qua
TuCompleteF10264F10264/0
3​
Qua
TuCompleteF10264F10264/0
1​
Fun
TuCompleteF10264F10264/0
1​
Fun
TuCompleteF10264F10264/0
1​
Fun
TuCompleteF10264F10264/0
2​
Fun
TuCompleteF10264F10264/0
1​
Fun
TuCompleteF10264F10264/0
2​
Fun
TuCompleteF10264F10264/0
1​
Fun
TuCompleteF10264F10264/0
1​
Fun
TuCompleteF10264F10264/0
1​
Fun
NDCompleteF10303F10303/0
1​
HOL
MoCompleteF10216F10216/0
15​
SMD
MoCompleteF10216F10216/0
1​
Qua
MoCompleteF10216F10216/0
1​
Qua
HyCompleteF10284F10284/0
1​
Inv
HyCompleteF10284F10284/0
1​
Inv
HyCompleteF10284F10284/0
2​
Inv
PeCompleteF10287F10287/0
1​
Fun
PeCompleteF10287F10287/0
1​
Fun
NDCompleteF10306F10306/0
1​
Qua
NDCompleteF10306F10306/0
2​
Qua
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,215,295
Messages
6,124,103
Members
449,142
Latest member
championbowler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top