CROISER 2 TABLES EXCEL DANS UN SEUL TABLEAU CROISE DYNAMIQUE

%3CLINGO-SUB%20id%3D%22lingo-sub-2360736%22%20slang%3D%22fr-FR%22%3ECROSS%202%20EXCEL%20TABLES%20IN%20A%20SINGLE%20DYNAMIC%20CROSSOVER%20TABLE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360736%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%2C%20I%20want%20to%20create%20a%20dynamic%20crossover%20table%20from%202%20excel%20tables%20located%20in%20the%20same%20excel%20binder%20(1%20table%20with%20name%20and%20date%20of%20birth%2C%201%20table%20with%20name%20and%20address)%3C%2FP%3E%3CP%3EI%20created%20a%20relationship%20between%20the%20two%20tables%20(the%20common%20key%20is%20the%20person's%20name)%20but%20when%20I%20create%20my%20tcd%2C%20it%20sends%20me%20back%20all%20the%20records%20(name%2C%20date%20of%20birth%20and%20all%20addresses.%3C%2FP%3E%3CP%3EI%20guess%20I%20zapped%20a%20step%20but%20which%20one%3F%3C%2FP%3E%3CP%3Ethank%20you%20in%20advance%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2379448%22%20slang%3D%22en-US%22%3ERe%3A%20CROISER%202%20TABLES%20EXCEL%20DANS%20UN%20SEUL%20TABLEAU%20CROISE%20DYNAMIQUE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379448%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055790%22%20target%3D%22_blank%22%3E%40murielhenaff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20a%20tcd%3F%20Please%20show%20us%20the%20steps%20in%20how%20you%20create%20the%20tcd.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2383240%22%20slang%3D%22fr-FR%22%3ERe%3A%20CROSS%202%20EXCEL%20TABLES%20IN%20A%20ONLY%20DYNAMIC%20CROISE%20TABLEAU%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2383240%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ea%20TCD%20is%20dynamic%20crossover%20in%20french...%20pivot%20table%20in%20english%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%202%20sheets%3A%3C%2FP%3E%3CP%3E-%20sheet%20MM%20with%20all%20products%20created%20for%20my%20organization%20with%20details%20such%20as%20weight%2C%20hierachy%2C%20category...%2C%3C%2FP%3E%3CP%3E-%20Sheet%20beg%20with%20same%20products%20and%20name%20of%20supplier%20(one%20product%20has%20only%20one%20beggar)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20relationship%20beetween%20those%202%20sheets%20through%20power%20pivot.%20The%20article%20is%20the%20common%20value.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22murielhenaff_0-1621941342166.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283467iED8A0ED943EA4037%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22murielhenaff_0-1621941342166.png%22%20alt%3D%22murielhenaff_0-1621941342166.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethen%20I%20need%20to%20create%20a%20pivot%20table%2C%20with%20material%20reference%2C%20description%2C%20hierarchy%2C%20...%20and%20name%20of%20beg.%3C%2FP%3E%3CP%3Ebut%20the%20results%20are%20wrong%20as%20each%20material%20is%20displayed%20to%20be%20linked%20to%20all%20suppliers%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EI%20guess%20that%20when%20creating%20the%20relationship%2C%20I%20need%20to%20get%20a%20one%20to%20one%20relationship.%20but%20I%20can't%20change%20this%20parameter%20(it%20is%20always%20multiple%20to%20one).%3C%2FP%3E%3CP%3Ecan%20you%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2384966%22%20slang%3D%22en-US%22%3ERe%3A%20CROISER%202%20TABLES%20EXCEL%20DANS%20UN%20SEUL%20TABLEAU%20CROISE%20DYNAMIQUE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384966%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055790%22%20target%3D%22_blank%22%3E%40murielhenaff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20does%20the%20expected%20result%20look%20like%3F%20What%20should%20the%20pivot%20result%20look%20like%3F%3C%2FP%3E%3CP%3EIn%20other%20words%20what%20is%20the%20parameter%20for%20the%20query%20(beggar%3F).%3C%2FP%3E%3CP%3Eexample%3A%20I%20am%20looking%20for%20product%20x%20to%20find%20out%20which%20supplier%20is%20providing%20product%20x%3C%2FP%3E%3CP%3Eor%20I%20want%20to%20see%20all%20products%20that%20the%20unit%20of%20measure%20is%20KG%2C%20or%20M2%2C%20or%20P%2C%20or%20PC%3C%2FP%3E%3CP%3Eso%20what%20is%20the%20beg%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2386772%22%20slang%3D%22fr-FR%22%3ERe%3A%20CROSS%202%20EXCEL%20TABLES%20IN%20A%20ONLY%20DYNAMIC%20CROISE%20TABLEAU%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2386772%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20result%20should%20show%20%3CSPAN%3Eall%20products%20with%20details%20like%20hierarchy%20from%20sheet%20MM%20-%20name%20of%20begging%20from%20sheet%20begging%20(for%20example%20records%20in%20red%20below%20are%20correct....%20supplier%20of%20product%2053252325%20is%20aeroglass%20only%2C%20beg%20from%20product%2053252684%20is%20IFT%20FILTERS%20only)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22murielhenaff_0-1622024552290.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283822i9B15716D19FBA310%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22murielhenaff_0-1622024552290.png%22%20alt%3D%22murielhenaff_0-162202452290.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2389040%22%20slang%3D%22en-US%22%3ERe%3A%20CROISER%202%20TABLES%20EXCEL%20DANS%20UN%20SEUL%20TABLEAU%20CROISE%20DYNAMIQUE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2389040%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055790%22%20target%3D%22_blank%22%3E%40murielhenaff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20the%20attached%20if%20that%20is%20or%20is%20close%20to%20what%20you%20were%20looking%20for.%3C%2FP%3E%3CP%3E%3CA%20title%3D%22Is%20This%20It%3F%22%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AiUZUhiQtF3Fgt503ym9Bnp2JWcYvg%3Fe%3D4KlYV8%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EPIVOT%20TABLE_Finale%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1622063501869.png%22%20style%3D%22width%3A%202160px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283994i19E383BCB4F1D14D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1622063501869.png%22%20alt%3D%22Yea_So_0-1622063501869.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2391663%22%20slang%3D%22fr-FR%22%3ERe%3A%20CROSS%202%20EXCEL%20TABLES%20IN%20A%20ONLY%20DYNAMIC%20CROISE%20TABLEAU%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391663%22%20slang%3D%22fr-FR%22%3Eyes%20that's%20it!%20Great!%20can%20you%20explian%20how%20you've%20managed%20this%20issue%3F%3C%2FLINGO-BODY%3E
New Contributor

Bonjour, je souhaite créer un tableau croisé dynamique à partir de 2 tables excel situées dans le même classeur excel (1 table avec le nom et la date de naissance, 1 table avec le nom et l'adresse)

j'ai créé une relation entre les 2 tables (la clé commune est le nom de la personne) mais quand je crée mon tcd, il me renvoie tous les enregistrements (le nom, la date de naissance et toutes les adresses.

j'imagine que j'ai zappé une étape mais laquelle?

merci par avance de votre aide.

7 Replies

Hi @murielhenaff 

What is a tcd? Please show us the steps in how you create the tcd.

@Yea_So 

 

a TCD is tableau croisé dynamique in french...pivot table in english

 

I've got 2 sheets:

- sheet MM with all products created for my organisation with details such as weight, hierachy, category...,

- Sheet supplier with same products and name of supplier (one product has only one supplier)

 

I have created a relation beetween those 2 sheets through power pivot. the article is the common value.

murielhenaff_0-1621941342166.png

then I need to create a pivot table, with material reference, description, hierarchy, ...and name of supplier.

but the results are wrong as each material is displayed to be linked to all suppliers

I guess that when creating the relation, I need to get a one to one relation. but I can't change this parameter (it is always multiple to one).

can you help me?

 

 

 

Hi @murielhenaff 

What does the expected result look like? What should the pivot result look like?

In other words what is the parameter for the query (beggar?).

example: I am looking for product x to find out which supplier is providing product x

or I want to see all products that the unit of measure is KG, or M2, or P, or PC

so what is the beg?

@Yea_So 

 

The result should show all products with details like hierarchy from sheet MM + name of supplier from sheet supplier (for example records in red below are correct....supplier of product 53252325 is aeroglass only, supplier from product 53252684 is IFT FILTERS only)

 

murielhenaff_0-1622024552290.png

 

 

 

thanks

Hi @murielhenaff 

Please check the attached if that is or is close to what you were looking for.

PIVOT TABLE_Finale 

 

Yea_So_0-1622063501869.png

 

yes that's it! great! can you explian how you've managed this issue?

Hi @murielhenaff 

 

There was no issue. I just checked the relationship, cleared your pivot table fields then selected nom1 first in the vendor table, then selected all the fields in MM.

 

Apparently in the TCD/Pivot Table, first field you select dictates what other related field will display. for example in your original TCD you selected the Articles field first, so the TCD displayed all Vendors related to the Article field. Now that you know how TCD behaves, you can now be mindful of what you are looking for. So if you wanted a list of MM related to the Vendors, then you have to choose a field in the vendors table first before it will list the related information about the first field selection. In your original TCD you selected Articles then the pivot table gave you a list of Vendors related to the Articles, however if you selected the Vendor nom1 first, then the TCD will display the records related to nom1 in the vendor table from the MM table.  

the key is i want a list of things from this list (a list within a list), so if you're shopping for apples and want to know who sells these apples, then you would select the apple article so you can see the list of vendors selling that apple, however in your case you wanted a list of products for each vendor so you have a vendor list and the TCD will list within each vendor what products they have available. 

 

To step this tutorial up a notch, you can then use Slicers or Measures.

You can use slicers to narrow your list down to specifics. So when you click an article slicers for example, you only want to see a list of vendors having to do with a specific article so the Slicer will filter through the list to display only the vendors that have a specific Paint, or other desired product.  With measures they are associated with calculated columns for example if your MM table had amounts of number of price per kilogram then you would create a calculated column, then click on measures if you want to see an article that is associated with the price per kilogram calculated column.

 

I hope I explained it to you clearly.

 

cheers