XL 2016 Actualiser et afficher automatiquement nouvelles colonnes TCD

[Etn]

Bonjour le forum,

J'ai mon tableau en A1 qui sert de base pour mon TCD qui a les dates dans la partie "nombre" du TCD pour faire les additions (en l'occurence l'année 2017)

Les dates du tableau de base sont basées sur l'année en cellule S4, et quand je change l'année (2018 par exemple), je souhaiterais que les nouvelles dates soient prises en comptes et directement intégrées dans mon TCD quand je l'actualise. Or actuellement quand j'actualise mon TCD, toutes les colonnes se reinitialise, et je dois tout recocher un par un (or dans mon fichier il y a beaucoup plus d'années et plusieurs tableaux).

Serait-ce possible d'automatiser l'affichage des nouvelles colonnes ?

Merci d'avance pour votre aide,
Etn

 

[Amilo]

Bonjour le forum, Etn,

Voici une proposition avec power query un peu tardive,
Pour cela, j'ai ajouté une ligne(2) qui est masquée actuellement pour préparer les tables power query : j'ai indiqué une série de valeurs dans cette ligne pour créer la relation entre 2 tables. J'ai mis AM1, AM2, AM3 pour Année, Mois et 1 (pour incrémenter une série) mais vous pouvez mettre toutes autres valeurs qui vous conviennent à la seule condition qu'elles soient uniques sans doublon

J'ai nommé 2 plages dans Excel : "Entetes" et "tbl_Source" mais là aussi vous pouvez les nommer autrement car par "plage_Entetes" et "plage_Source"
A ce stade comme il s'agit d'une plage nommée, la plage "tbl_Source" n'est pas dynamique si de nouvelles lignes ou colonnes venaient à se rajouter mais il est possible de la rendre dynamique soit en passant par une vraie table soit par une formule de plage dynamique.

Pour le reste, j'ai créé 3 requêtes query que je n'ai pas chargées dan Excel mais en connexion seulement.
Le TCD basée sur la requête est en vert.

Sinon, celui qui connait un peu Power Query, ce ne sera pas très compliqué de comprendre et de reproduire ces étapes.

P.S : j'ai réalisé le fichier avec la version Excel 2019 Pro Plus mais que vous pouvez réaliser avec Excel 2016 sans problème où Power query est également intégré.

Cordialement

 

[chris]

Bonjour à tous

Amilo tu as un peu compliqué je trouve...

J'ai mis la source sous forme de tableau en décochant en tête, ce qui ajoute une en-tête invisible

Ensuite j'ai juste eu à promouvoir la 1ère ligne comme en-tête puis décroiser, typer les données et renommer une colonne.

 

[Amilo]

Bonsoir Chris,

Merci pour votre retour et le fichier exemple simplifié,
Je suis passé par une plage nommée alors qu'habituellement je privilégie les tableaux structurés, sans doute histoire de montrer que l'on peut faire des requêtes aussi de cette manière

Concernant, l'option Promouvoir la 1ère ligne comme en-tête, j'avais eu des difficultés sur d'autres projets un peu plus complexes à savoir combiner plusieurs fichiers avec des noms d'en-entêtes différents (colonnes nommées avec des dates différentes d'un fichier à l'autre). Pour le coup, cela ne fonctionnait pas et j'avais un message d'erreur.
J'avais procédé donc autrement notamment des en-têtes sans nom défini.

Il est vrai que je me suis compliqué un peu et votre fichier est très simple.

Bonne soirée

 

[chris]

Re Amilo

Il est vrai que les date en en-tête posent problème, les tableaux structurés ne les acceptant pas.
Le second en-tête permet de palier mais a l'inconvénient de compliquer l'usage du tableau si on veut trier, filtrer...

On peut combiner nos 2 approches :

• en partant de ton idée, on peut nommer la plage source de façon dynamique avec DECALER et utiliser le nom comme tu l'as fait,
  
• traiter ensuite comme je l'ai fait

Ainsi c'est plus transparent pour l'utilisateur.

Pour le cas multi tables que tu évoques, le plus simple est d'ajouter une table calendrier. A partir de là on peut croiser plus facilement.

J'ai trouvé sur le net une fonction de génération de calendrier très complet mais Jean-Eric, sur un autre forum, a utilisé une technique astucieuse.
A partir d'un tableau, avec une date début et une date fin (éventuellement extrapolées des données à traiter), basculé dans PowerQuery

• changement de type de la date (colonne valeur) pour éliminer l'heure
  
• conversion en table : = Table.FromList(#"Changed Type", Splitter.SplitByNothing(), null, null, ExtraValues.Error)
  qui donne en numérique toutes les dates comprise entre début et fin
  
• changement de type pour repasser en date

 

[Amilo]

Bonjour Chris, le forum,

@chris, j'ai compris mon erreur car initialement j'étais parti sur la même base que vous à savoir en créant un tableau structuré et en décochant la case "Mon tableau comporte des en-têtes" pour avoir des noms de colonnes neutres "Colonne1, Colonne2,...etc".
J'ai vite abandonné car j'avais sans cesse le message d'erreur en capture ci-dessus lors du changement d'année et de l'actualisation de mon TCD.
Il fallait tout simplement supprimer les lignes que power query génère automatiquement et qui reprend les noms d'en-têtes avec les dates (voir capture "Code Editeur avance". Est-ce que j'avais supprimé une ligne seulement et pas l'autre…. pour au final passer à l'autre solution…!!!

Cependant, je ne comprends pas pourquoi, lors de la création de mon TCD, Power query crée automatiquement une 2ème requête Data (2)
Est-ce que c'est propre à Excel 2019 ?

Sinon, concernant les relations à un calendrier complet, je l'utilise déjà pour un autre dossier.
Celui évoqué, est un export de tables d'un site Web, avec des bizarreries à l'import. Les en-têtes de colonnes avec les dates que j'ai évoquées sont en réalité du texte écrites de cette manière "Dimanche 4 novembre2". Nous n'avons même pas l'année dans la colonne…
L'autre fichier aura comme en-tête une autre date du genre "3 nomvembre2"....etc
Je travaille sur un seul fichier pour les modifications puis crée une fonction pour appliquer aux autres fichiers structurés de la même manière.

Par contre, je reviendrai sur un prochain fil, pour voir s'il est possible d'importer plusieurs pages Web à la fois dans Power query.
Avec VBA je pense sans problème mais pas dans mes compétences.

Merci encore à vous
Bon dimanche

 

[chris]

re

Par défaut PowerQuery type les données en précisant les noms des colonnes.
Comme on va décroiser les données, je supprime immédiatement ce typage automatique et type après le croisement, ainsi pas de souci

Oui 2016 duplique systématiquement la requête alors que ce n'est pas le cas dans l'add on...
Il faudra que je regarde si on peut l'empêcher car je trouve cela casse pieds d'autant qu'on peut avoir à modifier la requête...

 

[Amilo]

Re,
Merci encore Chris pour toutes ces infos,
ça me rassure mais je ne comprends pourquoi Microsoft ait fait ce choix de dupliquer les requêtes avec les dernières versions, peut-être qu'il y a une raison….!

Cordialement

 

[chris]

RE

J'ai trouvé cela https://social.technet.microsoft.co...pivot-table-from-power-query?forum=powerquery

"We implemented this model in Excel 2016 (e.g., when creating a new PivotTable via Data > Existing Connections), but apparently a few holes left. This was one of them."

Holes semble signifier bug même si MS n'utilise pas ce mot...

 

[Amilo]

Re,

Effectivement, la même question soulevée par un internaute,
Je comprends aussi par là qu'il s'agirait d'un bug parmi d'autres….mais selon moi, des bug mineurs (c'est mon avis personnel)
Concernant, les conséquences de cet éventuel "bug", je pense le mieux est de procéder à différents tests sur le query original et de voir le résultat.

Bonne soirée