Power Pivot pulling extra/unwanted/unrelated data

%3CLINGO-SUB%20id%3D%22lingo-sub-2279543%22%20slang%3D%22en-US%22%3EPower%20Pivot%20pulling%20extra%2Funwanted%2Funrelated%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279543%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3EI%20am%20setting%20up%20a%20large%20Power%20Pivot%20file%2C%20and%20I%20am%20stuck%20fairly%20off%20the%20start%20as%20the%20information%20is%20not%20portraying%20itself%20logically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20database%20is%20a%20many%20to%20many%20relationship%2C%20so%20I%20have%20set%20up%20some%20unique%20tables%20being%20'Tbl_Sales_Unique'%2C%20'Tbl_Purchase_Inv_Unique'%2C%20and%20'Tbl_Distinct_CC'%20(Credit%20Card%20Id's)%20for%20the%20relationships.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20When%20I%20enter%20the%20Placement%20Invoice%20Number%20(PIN_%20and%20the%20Supplier%20Invoice%20Number%20(SIN)%2C%20they%20do%20not%20line%20up%20appropriately.%20e.g.%20PIN%2010013277%20only%20has%20SIN%20'g'%20associated%20to%20it%20in%20the%20data%20(Data%2014%2004)%20(See%20pivot%20on%20data%20above)%2C%20however%20in%20the%20Power%20Pivot%20it%20is%20associating%20SIN%20'g'%2C%20'h344'%2C%20'h5885'%2C%20'h6234'%2C%20and%20'blank'.%20What%20have%20I%20done%20wrong%20to%20cause%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20If%20a%20Purchase%20is%20made%20on%20Credit%20card%2C%20the%20Purchase%20Invoice%20in%20the%20Data%2014%2004%20file%20will%20be%20the%20same%20as%20the%20CC%20test%20data%2FTbl_Distinct_CC%20'Claim%20Id'.%20I%20am%20looking%20to%20pull%20in%20the%20Cr.%20Card%20Description%20into%20the%20file%20for%20any%20instance%20when%20there%20is%20a%20Cr.%20Card%20payment.%20However%20what%20has%20happened%2C%20is%20all%20of%20the%20descriptions%20are%20being%20pulled%20in%2C%20even%20if%20there%20is%20no%20Cr.%20card%20expense.%20e.g.%20SIN%20'g'%20has%20no%20cr.%20card%20payment%20however%20it%20shows%20all%20of%20the%20Cr.%20Card%20descriptions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%20'Power%20Pivot'%20Query'%20is%20the%20workings%2C%20all%20other%20files%20are%20support%20tables%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2279543%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hi,
I am setting up a large Power Pivot file, and I am stuck fairly off the start as the information is not portraying itself logically.

 

The database is a many to many relationship, so I have set up some unique tables being 'Tbl_Sales_Unique', 'Tbl_Purchase_Inv_Unique', and 'Tbl_Distinct_CC' (Credit Card Id's) for the relationships.

 

1) When I enter the Placement Invoice Number (PIN_ and the Supplier Invoice Number (SIN), they do not line up appropriately. e.g. PIN 10013277 only has SIN 'g' associated to it in the data (Data 14 04) (See pivot on data above), however in the Power Pivot it is associating SIN 'g', 'h344', 'h5885', 'h6234', and 'blank'. What have I done wrong to cause this?

 

2) If a Purchase is made on Credit card, the Purchase Invoice in the Data 14 04 file will be the same as the CC test data/Tbl_Distinct_CC 'Claim Id'. I am looking to pull in the Cr. Card Description into the file for any instance when there is a Cr. Card payment. However what has happened, is all of the descriptions are being pulled in, even if there is no Cr. card expense. e.g. SIN 'g' has no cr. card payment however it shows all of the Cr. Card descriptions.

 

File 'Power Pivot' Query' is the workings, all other files are support tables etc.

 

Thanks for any help!

0 Replies