1

Microsoft Power Pivot & Power Query Pour Microsoft

Dynamics Nav

Power BI est un ensemble de services et de fonctionnalités en ligne qui permettent à

l’utilisateur de rechercher et de visualiser des données, de partager des découvertes et de collaborer

en utilisant de nouvelles méthodes intuitives.

Dans le présent document, on introduit l’ensemble des fonctionnalités de Power BI présentes

dans Excel telles que le Power Pivot et le Power Query.

Power Pivot : créer des modèles de données élaborés dans Excel en créant des relations,

des mesures personnalisées, des hiérarchies et des indicateurs de performance clé. Les

modèles Power Pivot sont exécutés en mémoire pour permettre aux utilisateurs

d’analyser rapidement des millions de lignes de données.

Power Query : se connecter facilement à des données issues de sources de données

d’entreprise et publiques avec des fonctionnalités de recherche de données, et des

capacités facilitant la transformation et la fusion de données provenant de plusieurs

sources de données, afin de poursuivre leur analyse dans Excel.

Power Pivot et Power Query facilitent la découverte et la visualisation des données.

Microsoft BI Power Pivot

Power Pivot permet de créer des modèles de données à partir de diverses sources de données,

modélisées et structurées pour s’adapter précisément aux besoins des utilisateurs.

I. Démarrer le Pivot Power dans Microsoft Excel add-in

Power Pivot est un complément (add-in) qu’on peut utiliser pour effectuer une analyse des données

puissante dans Excel. L'add-in est intégré dans Excel, mais par défaut, il n’est pas activé. Voici comment

on active Power Pivot avant de l'utiliser pour la première fois :

1. Aller à File > Options> Add-Ins.

2

2. Dans la case Manage, on clique sur COM Add-ins> GO.

3. On sélectionne la case Microsoft Office Power Pivot for Excel 2013, puis on clique

sur OK.

3

4. Le ruban a maintenant un onglet Power Pivot.

5. On clique sur Manage pour ouvrir la fenêtre PowerPivot

II. Power Pivot permet de créer et de gérer une collection de tableaux et de relations à

partir de différentes sources de données. On va voir comment on peut charger nos

données dans Power Pivot à partir de Microsoft Dynamics NAV.

A partir de la fenêtre Power Pivot, on clique sur Obtenir des données externes (Get external

Data) afin d’utiliser l'Assistant Importation de table pour filtrer les données qui seront ajoutées au

fichier. On va tout d’abord choisir d’extraire des données à partir de SQL Server.

On lance l’Administration de Microsoft Dynamics NAV afin de récupérer le nom du serveur et

de la base sur lesquels on a une instance courante de Navision.

4

1. On va saisir ces informations dans la fenêtre d’importation des tables afin de se

connecter à la base et charger les données

On va choisir la liste des tables à importer. On peut sélectionner multiple tables et les

renommer. Pour notre cas, on sélectionne les tables Customer, Customer Bank Account et Calendar

Entry.

5

2. Les données des 3 tables de la base Navision ont été importées dans PowerPivot. Ces

données sont réparties dans 3 onglets de la fenêtre PowerPivot, un par table

(Customer, Customer Bank Account et Calendar Entry).

3. On peut ajouter des colonnes personnalisées et exploiter les fonctions d’Excel pour

faire le calcul, par exemple on fait le choix de chercher les années correspondantes

aux dates de la table Calendar Entry.

6

4. On sélectionne les colonnes vides dont on n’a pas besoin puis on les supprime en

cliquant sur le bouton droit puis sur « Delete Columns »

III. Créer des relations entre les tables

On utilise PowerPivot pour afficher et créer des relations entre les données de sources de

données différentes. Une relation est une connexion entre deux tables de données et qui établit le mode

de corrélation des données dans les deux tables. Par exemple, la table Customer et la table Customer

Bank Account ont une relation basée sur le fait que chaque client (customer) a un compte bancaire

(Customer Bank Account).

7

Les relations dans PowerPivot sont créées en établissant manuellement une jointure entre les tables

de la fenêtre PowerPivot ou les colonnes de la vue de diagramme, ou automatiquement, si PowerPivot

détecte des relations existantes lors de l'importation de données dans un classeur PowerPivot.

On crée une relation manuellement en établissant une jointure entre les deux tables qui contiennent

des données similaires ou identiques. Par exemple, les tables Customer et Customer Bank Account

sont associées par les colonnes Numéro (No, Customer No) qui sont présentes dans les deux tables.

Ces colonnes ne doivent pas forcément porter le même nom.

Il suffit d’aller à Home > Diagram View > Glisser une flèche entre les champs

Ou bien aller à Design > Create Relationship > Sélectionner les champs des tables désirés

8

IV. Créer des tableaux et graphiques croisés dynamiques.

Une Table croisée dynamique (Pivot Table en anglais) est une table de feuille de travail

interactive qui résume rapidement de grandes quantités de données à l'aide de méthodes de calcul

qu’on choisit. On peut faire pivoter ses lignes et ses colonnes autour du noyau des données afin de

donner des différentes vues des données source.

1. Dans le volet « Home » de PowerPivot, on clique sur Pivot Table

2. L’écran suivant apparaît, on remarque bien que power pivot a importé les trois tables

dans une nouvelle feuille d’Excel afin d’utiliser leurs champs

9

3. On organise les champs pour aider à analyser les données.

4. Ensuite, on peut exploiter les fonctions d’Excel pour insérer des graphes, des slicers, etc.

5. De la même façon que les tables croisées, Power Pivot offre la possibilité d’insérer

directement des graphes dynamiques

10

Microsoft BI Power Query

Power Query permet de partager, de gérer des requêtes, et de rechercher des données au sein

d’une organisation. Les utilisateurs de l’entreprise peuvent rechercher et utiliser ces requêtes

partagées (si celles-ci sont partagées avec eux) pour les données sous-jacentes dans des requêtes à

des fins d’analyse de données et de création de rapports.

En utilisant plusieurs sources de données, on peut filtrer, mettre en forme, fusionner et ajouter ces

données à l’aide de Power Query, sans les charger dans Excel. On a la possibilité d’explorer les données,

qu’on peut mettre en forme souhaitée et envoyer directement au modèle de données dans Power

11

Pivot. Et même dans ce cas, on peut créer de nouveaux calculs et champs dans Power Pivot de manière

à n’obtenir que l’ensemble de données souhaité.

Il suffit d’activer Power Query dans Excel ou bien télécharger une version et l’installer, elle sera activée

automatiquement et intégrée à Excel.

I. Charger et manipuler des données à partir du web

1. On commence par ouvrir un classeur Excel 2013 vierge et accède à l’onglet Power Query.

Power Query permet aux utilisateurs de rechercher, de combiner, d’affiner, de mettre en

forme et de télécharger des données issues de toutes sortes de sources et types de données.

Sous l’onglet Power Query, On lance une nouvelle recherche.

2. Power Query permet de rechercher des données publiques provenant de sources sur

internet… Par exemple, on peut copier le lien d’une page wikipedia et le coller dans la zone de

recherche

12

Excel demande de se connecter à la page wikipedia, on clique sur se connecter.

3. Powerpivot importe les données qu’ils détecte sous forme de tableaux

4. Dès qu’on clique sur « charger », les tableaux vont être importées dans la feuille d’Excel,

chaque table est considérée par défaut une requête de classeur

13

5. On peut insérer des tableaux croisés et faire des analyses à partir de cette nouvelle feuille de

classeur

6. A partir de ces tableaux croisés on insère des graphes, et comme ça on obtient des statistiques

au bout de 3 minutes.

V. Comment on crée des rapports avec Power Query à partir de Microsoft Dynamics NAV.

1. La première étape consiste à extraire les données à partir de la base Microsoft SQL Server

(celle de MSDN NAV)

14

On lance l’Administration de Microsoft Dynamics NAV afin de récupérer le nom du serveur et

de la base sur lesquels on a une instance courante de Navision.

2. On va saisir ces informations dans la fenêtre d’importation des tables afin de se connecter à la

base et charger les données

15

3. On va choisir la liste des tables à importer à partir de la base CRONUS apparue en résultat. On

peut rechercher et sélectionner multiple tables et les renommer. Pour notre cas, on

sélectionne les tables Customer, Customer Bank Account et Calendar Entry comme on a fait

précédemment avec Power Pivot.

4. Les tables résultantes sont réparties dans les feuilles du classeur Excel. Prenons l’exemple de

la table « Customer » :

5. On peut retourner sur PowerQuery en sélectionnant la requête qui correspond à la table

« Customer » dans le volet requêtes de classeur, et comme ça on pourra faire des modifications

sur la table résultante.

16

Changer le type d’une colonne : Texte => Date/Heure

17

Supprimer des colonnes

Supprimer les doublons de la même colonne

Fusionner deux tables : Customer et Customer Bank Account

18

Ajouter des colonnes à partir de la table Customer Bank Account à la table Customer

19

-

Regrouper les colonnes d’une table selon des opérations spécifiées par l’utilisateur

Consulter l’historique des modifications

Power Query décrit la source de données, en l’occurrence un fichier Excel, comme Binaire.

20

Pivot table est aussi applicable sur les tableaux construits à partir de Power Query, on précède

de la même façon et on crée une table croisée partant des données sélectionnées.

Finalement, on choisit des graphes selon les champs interprétés.

21

--------------------------------------------------------------------------------------------------------------------------------------

Créé le 21/08/2015 Par Maroua Bouhachem

Maroua BOUHACHEM

Software Engineer

https://www.linkedin.com/in/marouabouhachem