Many to Many Relationship

Status
Not open for further replies.
K

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-129 January 20215179.58
NDF102982193-1-129 January 20215429.58
NDF103102654-1-129 January 20213617.38
ND2765-1-106 March 2021203.87
NDF103032710-3-112 February 2021865.68
NDF103122711-3-112 February 2021865.68
NDF103122712-3-112 February 2021865.68
NDF103122713-3-112 February 2021865.68
NDF103132819-1-112 March 20213658.62
NDF103132828-1-119 February 20215429
NDF103172859-2-119 March 20210
ND2021022859-6-119 March 2021150.61
NDF103142869-1-112 March 20213617.38
NDF103142905-1-112 March 20213814.76
ND2021022905-2-112 March 2021101.51
NDF103142935-10-119 March 20212227.65
NDF103142935-1-119 March 20212227.65
NDF103142935-11-119 March 20212227.65
NDF103142935-3-119 March 20212227.65
NDF103142935-7-119 March 20212227.65
NDF103142935-9-119 March 20212227.65
NDF103102917-1-125 February 2021481.99
NDF103152941-1-119 March 20212684.11
NDF103082941-2-119 March 2021200.5
NDF103152955-1-119 March 20213573.71
ND2020122955-2-119 March 2021176.26
NDF103082957-2-119 March 2021200.5
NDF103162978-1-119 March 20212998.66
ND2021032978-2-119 March 2021101.51
ND2020042985-2-126 February 20213.96
ND2020112978-3-119 March 2021128.04
NDF103183104-1-101 March 20215429.58
ND2021032982-1-119 March 2021101.51
NDF103162982-2-119 March 20213776.45
NDF103162984-1-119 March 20214506.67
NDF103082984-2-119 March 2021200.5
ND2020112993-1-112 March 202159.42
NDF103082993-2-112 March 2021651.68
NDF102972999-6-108 March 202123.68
NDF103173011-1-119 March 20212488.12
NDF103173046-1-119 March 20212550.96
NDF103152953-1-105 March 20212425.45
NDF103083067-1-105 March 2021200.5
NDF103152953-2-105 March 20212425.45
NDF103152954-1-105 March 20211503.52
NDF103183101-1-112 March 2021399.74
NDF103083101-2-112 March 2021651.68
NDF103082578-1-105 March 20214069.17
NDF103142935-6-105 March 20212227.65
NDF103142935-8-105 March 20212227.65
NDF103162972-1-112 March 2021205.66
ND*Short2990-1-112 March 202182.94
NDF103093117-2-119 March 2021543.39
NDF103152942-1-119 March 20214010.29
NDF103082942-2-119 March 2021200.5
ND2812-2-122 March 2021176.26
NDF103152812-3-122 March 2021865.68
NDF103082812-4-122 March 2021200.5
ND2020122816-2-123 March 2021176.26
NDF103082816-3-123 March 2021200.5
NDF103132816-1-125 March 20215124.18
NDF103132814-1-126 March 20214420.53
ND2815-2-126 March 2021176.26
NDF103082815-3-126 March 2021200.5
NDF103032815-4-126 March 2021865.68
NDF103072553-1-126 March 20215179.58
NDF102901801-1-126 March 20211924.33
NDF102901801-2-126 March 20211924.33
NDF103152956-1-126 March 20213735.95
NDF103142936-1-126 March 20213036.45
NDF103152945-1-126 March 20212786.24
ND2021032945-2-126 March 2021101.51
NDF103132800-1-131 March 20213054.92
NDF103132800-2-131 March 20213054.46
NDF103173007-1-102 April 20213087.81
NDF103123007-2-102 April 2021200.5
NDF103173038-1-109 April 20213596.12
NDF103132801-1-121 April 20213617.38
NDF103132801-2-121 April 20213617.38
NDF103152936-2-123 April 20213036.45
ND2021013088-3-130 April 202151.41
ND2554-5-112 March 202124.34
ND2971-1-105 March 20211553.88
ND2709-3-112 February 2021865.68
GE0822-3-302 March 2021758
Ta*Short1508-2-113 November 2020126.99
SiF102991935-1-213 November 20202278.65
MoF102359789-2-3208 January 2021204.33
MiF102772759-1-120 January 20211269.87
MiF102772760-1-120 January 20211269.87
MiF103122761-2-120 January 2021135.64
MiF103172861-1-124 February 20211283.23
MiF103182861-5-103 February 202150.02
Mo3040-1-119 February 20210
ThF103002222-1-102 February 2021866.89
No2021012465-11-319 February 202117.98
Th3089-1-111 March 2021156
Th3036-1-112 March 2021358.02
MiF103173086-1-124 March 2021709.48
MiF103173085-1-131 March 2021345.89
GE3002-1-331 March 202126197.9
Am1630-3-130 November 20209123.05
Am2021020840-1-1115 January 2021651.06
TaF102831508-1-613 November 20201777.86
TaF102831508-1-613 November 20201777.86
TaF102831508-1-613 November 20201777.86
Mi2020121003-1-613 November 20202212.1
Mi2020121003-1-613 November 20202212.1
MiF102541003-1-613 November 20202212.1
MiF102751003-1-613 November 20202212.1
MiF102751003-1-720 November 20202875.73
TaF103062112-4-116 December 20201022.13
PeF102962401-1-115 January 20211158.54
PeF103022401-1-115 January 20211158.54
MoF102366721-1-3419 March 20216007.55
Am1630-2-130 November 202027217.44
FoF103032360-1-105 February 20212234.24
FoF103062495-1-102 March 2021983.5
TuF102640498-1-4316 November 2020935.28
TuF102640498-1-4316 November 2020935.28
GrF103112671-1-105 March 202111599.5
TuF102630498-3-3914 December 20201843.09
TuF102630498-3-3914 December 20201843.09
TuF103102680-1-124 December 20200
TuF102640498-1-1718 January 20214676.4
TuF102640498-1-1718 January 20214676.4
TuF102640498-1-1718 January 20214676.4
TuF102640498-1-1718 January 20214676.4
TaF103062112-4-218 January 2021567.85
TaF102972112-1-522 January 20216132.78
TaF102972112-1-522 January 20216132.78
MoF102359789-2-3323 February 20211838.97
MoF102351856-2-102 March 2021408.66
ThF102902273-1-308 February 2021446.91
TuF102640498-1-1815 February 20218417.52
Tu2021030498-10-1415 February 202199
TuF102630498-3-1515 February 20213317.56
TuF102640498-4-1915 February 20211117.12
TuF102640498-4-1915 February 20211117.12
TuF102630498-7-1515 February 2021651.75
NoF103052465-5-1219 February 2021204.3
TaF103062515-1-319 February 2021695.1
TaF103062112-4-319 February 20212271.4
TaF102972112-1-619 February 202115331.95
TaF102972112-1-619 February 202115331.95
ThF103112682-1-125 February 20211124.4
ThF102922500-1-526 February 20212712.68
ThF102922500-1-526 February 20212712.68
NoF103052465-7-126 February 20212431.34
ThF102922898-1-126 February 2021953.3
ThF103012312-1-301 March 20211215.94
ThF102851187-1-801 March 202196000
PeF102962057-1-202 March 202113350.5
PeF102872057-1-902 March 2021801.03
PeF102962057-1-902 March 2021801.03
NoF103042465-6-205 March 20213159.45
ThF103112847-1-105 March 20211124.4
NoF103052465-7-205 March 20214290.6
NoF103042465-9-205 March 2021404.4
PeF102962057-1-308 March 20215340.2
ThF102931901-1-610 March 202119693.35
NoF103002313-1-519 March 20211028.16
NoF103002313-1-519 March 20211028.16
OxF103022718-1-412 March 20218264.01
NoF103042465-8-212 March 2021717
OxF103022718-1-519 March 20218264.01
ThF103122725-1-115 March 20212742.3
ThF103132835-1-115 March 2021347.4
ThF103092604-1-117 March 2021580.8
TaF103142873-1-119 March 202111357
TaF103062112-4-419 March 20212271.4
TaF103062515-1-419 March 20213475.5
TaF102972112-1-719 March 202117035.5
TuF102640498-1-1922 March 20218417.52
Tu2021030498-10-1522 March 2021594.17
TuF102630498-10-1522 March 2021594.17
TuF102630498-2-1922 March 20217045.02
Tu2021020498-2-1922 March 20217045.02
Tu2021020498-2-1922 March 20217045.02
Tu2021010498-2-1922 March 20217045.02
TuF102630498-2-1922 March 20217045.02
TuF102630498-3-1622 March 20213317.56
TuF102640498-4-2022 March 20215027.09
TuF102640498-4-2022 March 20215027.09
TuF102640498-4-2022 March 20215027.09
TuF102640498-4-2022 March 20215027.09
Tu2021020498-5-1822 March 20211589.22
TuF102640498-5-1822 March 20211589.22
Tu2020110498-6-1622 March 2021593.6
Tu2020120498-6-1622 March 2021593.6
TuF102640498-6-1622 March 2021593.6
TuF102630498-7-1822 March 20213910.66
Tu2021030498-8-2022 March 20212816.55
TuF102640498-8-2022 March 20212816.55
Tu2021030498-9-1622 March 20211736.32
Tu2021030498-9-1622 March 20211736.32
ThF103018586-1-1522 March 2021120273.2
MiF103172861-2-124 February 20211383.56
OxF103022998-1-126 March 202111018.68
No2021022465-11-426 March 2021719.2
No2021022465-11-426 March 2021719.2
TaF103142873-1-230 March 202111357
Pe0733-1-108 March 20211362.25
Pe0733-1-208 March 2021163.47
Pe0733-1-308 March 20213977.77
Th1444-1-1005 March 2021179.55
Th2075-1-801 March 2021790.86
NDF102942058-5-111 January 20215288.02
NDF102942058-6-111 January 2021552
NDF103102182-13-119 March 20214055.5


Sheet 2

NameStateJobbatchQuantityTitle
MiHOLDF10279F10279/05Cle
MiHOLDF10299F10299/011HOL
ThHOLDF10276F10276/020HOL
PeHOLDF10296F10296/050HOL
ThHOLDF10308F10308/020HOL
TaHOLDF10307F10307/0300HOL
PeHOLDF10296F10296/040HOL
NDHOLDF10310F10310/0200HOL
NDHOLDF10303F10303/06HOL
TaHOLDF10307F10307/0300HOL
HyHOLDF10263F10263/08Box
OnHOLDF10256F10256/090HOL
OnHoldF10260F10260/019HOL
ThHOLDF10276F10276/010Box
HyHOLDF10316F10316/040HOL
HyHOLDF10316F10316/040HOL
HyHOLDF10316F10316/015HOL
HyHOLDF10316F10316/055HOL
HyHOLDF10311F10311/010HOL
ThHOLDF10285F10285/08Box
NDHOLDF10313F10313/02HOL
NDHOLDF10313F10313/01HOL
OnHOLDF10295F10295/080HOL
NDHOLDF10161F10161/05HOL
ThHOLDF10285F10285/08Box
NDHOLDF10313F10313/01HOL
OnHOLDF10251F10251/01HOL
ThHOLDF10285F10285/08Box
ThHOLDF10285F10285/08HOL
ThHOLDF10285F10285/08HOL
HyHOLDF10311F10311/010HOL
NDHOLDF10315F10315/05HOL
TuHOLDF10314F10314/010HOL
ThHOLDF10285F10285/08Box
NDHOLDF10314F10314/075HOL
ThHOLDF10294F10294/020HOL
ThHOLDF10312F10312/030HOL
SoHOLDF10288F10288/010HOL
ThHOLDF10315F10315/030HOL
ThHOLDF10289F10289/08HOL
OnHOLDF10295F10295/07HOL
OnHOLDF10254F10254/04HOL
OnHOLDF10254F10254/04HOL
OnHOLDF10277F10277/075HOL
ThHOLDF10292F10292/020Con
ThHOLDF10300F10300/030HOL
ThHOLDF10292F10292/020HOL
ThHOLDF10292F10292/020HOL
MiHOLDF10317F10317/05HOL
SoHOLDF10272F10272/010HOL
OnHOLDF10295F10295/010HOL
OnHOLDF10295F10295/010HOL
OnHOLDF10254F10254/03HOL
ThHOLDF10285F10285/08HOL
NDHOLDF10312F10312/02HOL
OnHOLDF10251F10251/030HOL
ThHOLDF10289F10289/06HOL
OnHOLDF10251F10251/060HOL
OnHOLDF10295F10295/05HOL
OnHOLDF10295F10295/02HOL
TuCompleteF10264F10264/01Qua
TuCompleteF10264F10264/01Qua
TuCompleteF10264F10264/01Fun
NDCompleteF10290F10290/029Box
MiCompleteF10234F10234/04Inv
OnCompleteF10277F10277/01Fun
OnCompleteF10277F10277/01Qua
OnCompleteF10277F10277/02Fun
OnCompleteF10277F10277/01Fun
OnCompleteF10277F10277/01Fun
MiCompleteF10299F10299/01Box
MiCompleteF10240F10240/015Pac
SoCompleteF10272F10272/01Qua
SoCompleteF10272F10272/01Fun
SoCompleteF10272F10272/00Qua
NDCompleteF10290F10290/01HOL
NDCompleteF10290F10290/01HOL
TuCompleteF10263F10263/01Qua
TuCompleteF10264F10264/03Qua
TuCompleteF10264F10264/01Fun
TuCompleteF10264F10264/01Fun
TuCompleteF10264F10264/01Fun
TuCompleteF10264F10264/02Fun
TuCompleteF10264F10264/01Fun
TuCompleteF10264F10264/02Fun
TuCompleteF10264F10264/01Fun
TuCompleteF10264F10264/01Fun
TuCompleteF10264F10264/01Fun
NDCompleteF10303F10303/01HOL
MoCompleteF10216F10216/015SMD
MoCompleteF10216F10216/01Qua
MoCompleteF10216F10216/01Qua
HyCompleteF10284F10284/01Inv
HyCompleteF10284F10284/01Inv
HyCompleteF10284F10284/02Inv
PeCompleteF10287F10287/01Fun
PeCompleteF10287F10287/01Fun
NDCompleteF10306F10306/01Qua
NDCompleteF10306F10306/02Qua
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Status
Not open for further replies.

Similar threads

K
  • Question Question
many-to-many relations and visualization cross-filtering issue
Replies
0
Views
357
krrb4u
K
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
H
  • Question Question
Calendar userform-text selection
Replies
1
Views
7K
hissonrr
H

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

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