Conserver les données lors d’une liaison entre des données externes de type CSV et PowerQuery

Bonjour à tous,

Après 3 enregistrements ratés, je pense que ce dernier est le bon ! Je suis certain que l'on peut encore améliorer, mais cette approche devrait énormément vous aider dans les liaisons entre vos bases externes et Excel.

Objectif : connecter une requête PowerQuery à une base externe, puis, dans Excel, ajouter des colonnes stockant des valeurs et des formules, pour finalement conserver la correspondance de ces données lors de l'actualisation.

Cela permet de compléter une base de données existantes en stockant les données qui vous sont propres en local sur votre fichier. Puis, au final, à proposer une solution quasiment clef en main au service IT pour améliorer vos bases de données et intégrer vos changements.

Ces trois vidéos font suite à plusieurs autres posts (idée originelle en Anglais, puis en Français, ici, ici et ) et résolvent le problème de l'ajout de nouvelles colonnes tout en conservant les formules.

Voici les fichiers utilisés

Vidéo expliquant la mise en place du fichier en 3 étapes

Explication du fichier en 10 minutes

Construction intégrale en 45 minutes

En bas à droite de la vidéo, vous pouvez agrandir (recommandé), gérer un menu et la vitesse.

Ce que j'ai appris depuis la vidéo (intégré dans le modèle)

  • Il existe une fonction ESTFORMULE. Ca simplifie considérablement ma formule dans les paramètres.
  • Il existe une fonction en M qui renvoit automatiquement le nom des colonnes d'une table. le résultat est une liste, donc pratique :
    Table.ColumnNames(Nom_Table)

Identifier les problèmes de taille dans un fichier Excel

Bonjour à tous,

Voici une fonctionnalité que je commence à comprendre de mieux en mieux et dont je découvre les limites.

Comme l'a si bien dit l'oncle de notre ami entoilé, "un grand pouvoir implique de grandes responsabilités". Ici, c'est pas mal le cas, donc attention avant d'utiliser cet outil.

Vidéo récapitulative

Vous trouverez le détail plus bas, mais voici une vidéo récapitulative de cette fonction et de ses limites.

Le bouton est ici, dans l'onglet Révision:

Alternativement, ALT+Q, puis taper "Vérifier les performances" ou simplement "performance" :

Après analyse, une liste d'améliorations va apparaitre sur la droite. On peut ensuite appliquer les améliorations sur la feuille ou sur l'intégralité du classeur.

Je viens de tester sur deux gros fichiers, les modifications concernent principalement des mises en forme et des redimensionnements de tailles de lignes.

Voici quelques limites importantes pour cet outil puissant, mais à manier avec la plus grande prudence !

Couche des objets

Dans mes premiers tests, il y a des effets de bord si des formats de cellules inutiles se superposent ou font implicitement référence à des objets. Par exemple, il peut n'y avoir aucune donnée sur la couche des cellules, mais il peut y avoir des objets sur la couche des objets (graphiques, zone de texte, segments, images, etc) et ils seront impactés. De fait, la proposition de modification de format n'est pas appropriée.

Je détaille quelques cas ci-dessous.

Suppression de titres sur la droite.

Segments, zone de texte et hauteur de lignes

Ajustements de lignes pouvant créer des problèmes. Par exemple, si vous mettez des objets (e.g. segments ou textbox) dans la ligne 1 - qui ne contient pas de texte - et que vous en augmentez la hauteur. Le vérificateur de performance va proposer de réduire la taille de la ligne 1 ce qui va avoir pour effet de rendre invisible vos objets (si la propriété redimensionner de l'objet est cochée).

Image et hauteur de lignes

Encore un autre effet de bord avec les images (toujours dans la même veine de la couche des objets qui est impactée par un redimensionnement).

Par exemple, ici, les images sont redimensionnées à cause de la mise en forme des lignes, ce qui impacte l'image dynamique liée.

Suppression du format alors qu'ils étaient utiles

Disparition de vos magnifiques Mondrians ou pixel art !

Indiqué sur le site de microsoft, les pixel arts (sans contenu dans les cellules) sont destinés à disparaitre avec ce type d'optimisation

Disparition du format de cellule de saisie vide

J'ai eu le cas où je dois saisir une adresse dans une cellule formatée, mais qui reste vide la plupart du temps.

Pas de gestion des formules propagées

Celle là est aussi assez spécifique, et je ne vois pas comment la parer, car l'extension d'une plage dynamique propagée est quasiment impossible à prévoir. Ici, on a une plage dynamique avec SEQUENCE qui dépend de B1. Donc, si je change la valeur de B1 pour 6, la mise en forme en B9:B12 est identifiée comme inutile, alors que ca n'est pas le cas puisque je peux augmenter la valeur de B1 pour 10 et donc avoir besoin du formatage.

Les mises en forme conditionnelles ne sont pas identifiées par le vérificateur, même s'il n'y a aucune données dans la plage.

Ce qui est une bonne chose, je pense.

Pas de modification au niveau d'une plage spécifique

Je n'ai pas non plus trouvé comment appliquer certaines des modifications et non pas toutes les modifications de la feuille.

Attention aux utilisateurs non-avertis

Des utilisateurs non-avertis peuvent supprimer involontairement des formats nécessaires en voulant optimiser en une fois tout le fichier. Je connais certains utilisateurs qui n'hésiteraient pas !

Verdict : très intéressant, mais à manipuler avec beaucoup de précautions

L'outil reste donc utile et pertinent, mais aussi potentiellement dangereux si vous ne faites pas attention ou si vous le laissez dans les mains d'un utilisateur non-averti.

Une autre approche si vous faites beaucoup de développement

Il existe aussi Inquire, mais cet outil nécessite une certaine version d'office. D'après Microsoft, la nouvelle fonction de vérification de performance va plus en profondeur dans l'analyse.