Power Query et Power Pivot sont des compléments qui proviennent de Power BI et qui ont été depuis mis à disposition des utilisateurs "normaux".

Power Query

Il est intégré nativement dans les fonctionnalités Excel depuis la version 2016.

C’est un outil qui permet de travailler vos longues listes de données avant de les charger dans Excel. Vous pouvez ainsi les modifier "virtuellement" dans une plate-forme de travail et lorsque le résultat vous convient vous pouvez "passer" dans Excel.

Comment travailler dans Power Query ?

Onglet DONNEES - Groupe RECUPERER ET TRANSFORMER DES DONNEES - Bouton OBTENIR DES DONNEES.
Les données source peuvent provenir de programmes variés : une liste ou un tableau Excel, Access, Azure, ODBC ...

Caractéristiques :

• Power Query travaille avec des requêtes.
• Le résultat final est toujours un tableau Excel et non une liste simple.
• Chaque commande ou étape est mémorisée et peut être supprimée.
• Gestion des colonnes : choisir les colonnes à garder, les déplacer à volonté, ne garder que des lignes spécifiques, trier, filtrer, fractionner ...
• Gestion du contenu d'une colonne : ne garder que des éléments spécifiques pour des dates, remplir avec des données manquantes (évite des copier-coller toujours un peu dangereux), passer de min à maj et vice versa très facilement ...
• Gestion de la structure de la liste : pivoter ou dépivoter une colonne, fractionner ou fusionner (sans la lourdeur d'un CONCATENER) ...
• Gestion de plusieurs listes ou tableaux sources : données un peu similaires ? On peut ajouter les requêtes pour ne faire plus qu'un seul tableau final. Données différentes mais avec une colonne commnune ? On peut fusionner afin de ne mettre en relation que les informations souhaitées de part et d'autre.

Power Pivot

Power Pivot est un complément qui ajoute des fonctionnalités de modélisation des données dans Microsoft Excel.

Power Pivot présente 2 grands points forts

Traitement des données volumineuses (Big Data)

• Excel présente une limite du nombre de lignes d’une feuille de calcul à 1'048'576. Power Pivot permet d’importer des données volumineuses de sources externes diverses et pouvant contenir plusieurs millions de lignes.
• Leur traitement est particulièrement rapide.
• Les données étant fortement compressées, les fichiers créés présentent une taille gérable.
• Les données sont enregistrées à l’intérieur du classeur Excel, ce qui assure leur portabilité.

Modèles de données avec relations

La création d’un tableau croisé dynamique se fait en général par rapport à UNE liste (définie ou non en tableau). Mais dans la réalité, les informations sont souvent dispersées dans plusieurs listes, parfois très volumineuses : le tableau 1 donne le client, les données le concernant et ce qu’il a commandé mais c’est le tableau 2 qui fournit le code du produit et son prix unitaire et encore un tableau 3 qui indique la catégorie à laquelle appartient le produit avec peut-être des informations comme les mesures, la couleur etc. Impossible de créer un tableau croisé dynamique entre les données du tableau 1 et 3 par exemple ! Il faut d'abord mettre les tables en relation. On peut le faire dans Excel, sans savoir que l'on fait appel à Power Pivot en arrière-plan ! On peut également travailler directement dans Power Pivot, qui offrira des fonctionnalités additionnelles intéressantes. Que ce soit dans Excel ou PowerPivot, on appelle ceci un modèle de données.
C'est vrai, on pourrait obtenir la même chose avec des RECHERCHEV mais cela serait tout de même long et fastidieux. De plus cette fonction compte physiquement la position de la colonne recherchée (c’est la colonne 3 ou 4 … ou 15) ce qui la « rigidifie » passablement en cas de modifications des listes source.

Et si vous êtes un modeleur de données expérimenté (fonctions DAX, SQL) Power Pivot offre de nombreuses fonctions de modélisation plus avancées.

Où trouver Power Pivot ?

• Excel 2019 et 365
  Installé et activé nativement : onglet Power Pivot dans le ruban.
  Autre accès : bouton GÉRER dans l’onglet DONNÉES – Groupe OUITLS DE DONNÉES.
• Excel 2013 et 2016
  Il est installé nativement mais il n’est pas activé.
  Pour l’activer, il faut aller dans les Compléments COM (Fichier - Options - Compléments).
  (à contrôler si vous avez le droit de le faire vous-même ou si vous devez passer par votre service informatique).

Lier plusieurs tables - Vidéo sur YouTube à visionner et fichier exemple Excel à télécharger

• Vidéo : Power Pivot dans Excel - Lier des tables
• Fichier Excel : Fichier Excel de la vidéo

Si vous souhaitez en discuter avec moi : Contact (mail ou tel)

Pour plus d'informations Consultez ma page des cours privés

Pour visionner divers tutos bureautiques Aller à la section TUTOS