Rappelez-vous 60
Synergie - Excel Services / Excel Online - Parlons peu mais parlons BI
Connaissez-vous Power Query ?
Microsoft Power Query pour Excel est un add-on d'Excel qui permet d'extraire, de transformer, d'enrichir des données
et de les importer directement dans un classeur Excel et ce quelle que soit la source de ces données. Et de Excel à
SharePoint il n'y a qu'un petit pas à franchir.
C'est un ETL au sens http://fr.wikipedia.org/wiki/Extract_Transform_Load totalement gratuit et extrêmement intuitif.
Mais pour moi c'est surtout le couteau suisse du poweruser, le « multipass » de l'analyste (du futur proche) des
données qui maîtrise parfaitement la synergie SharePoint/Excel.
La BI libre-service à portée de tous. Ou comment ramener facilement des données dans SharePoint avec une facilité
déconcertante.
Pour ce faire il faut avant toute chose disposer d'Excel 2013 ou plus et installer Power Query en allant tout simplement
le télécharger ici : http://www.microsoft.com/fr-FR/download/details.aspx?id=39379
Une fois installé, vous devez obtenir le ruban suivant avec Power Query en bonne place :
Puis en cliquant sur Power Query le menu s'affiche :
1
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
D'où peut-on extraire des données ?
Réponse : de partout du moment qu'on y a accès.
La liste des possibles :
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
o
Se connecter à une page web
Se connecter à un fichier Excel ou CSV
Se connecter à un fichier XML
Se connecter à un fichier texte
Se connecter à un dossier
Se connecter à une base de données SQL Server
Se connecter à une base de données Microsoft Azure SQL
Se connecter à une base de données Access
Se connecter à une base de données Oracle
Se connecter à une base de données IBM DB2
Se connecter à une base de données MySQL
Se connecter à une base de données PostgreSQL
Se connecter à une base de données Sybase IQ
Se connecter à une base de données Teradata
Se connecter à une liste SharePoint
Se connecter à un flux OData
Se connecter à un flux OData de Dynamics CRM
Se connecter à Microsoft Azure Marketplace
Se connecter à un système HDFS (Hadoop Distributed File System)
Se connecter à Microsoft Azure HDInsight
Se connecter au stockage d'objets blob Microsoft Azure
Se connecter au stockage de tables Microsoft Azure
Se connecter à Active Directory
Se connecter à un compte Microsoft Exchange Server
Se connecter à Facebook
Se connecter à une table de données Excel
Essayons les deux premiers WEB et CSV :
Je vous propose d'aller chercher la table officielle des codes postaux https://www.data.gouv.fr/fr/datasets/baseofficielle-des-codes-postaux/
Ouvrez Excel et Power Query
2
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
Cliquez sur importer des données d'une page web.
Et copiez-y l'adresse du lien :
3
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
L'éditeur de requête s'ouvre :
Dans la partie droite vous avez les paramètres de la requête : changez son nom.
4
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
Cliquez sur le bouton Source avec le bouton droite de votre souris.
Le moteur de l'éditeur a appliqué les instructions automatiques de détection du délimiteur, du type et du nom des
colonnes. Ce qui se traduit par :
Ou si vous cliquez sur Editeur avancé par :
5
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
let
Source = Csv.Document(Web.Contents("https://www.data.gouv.fr/s/resources/base-officielle-des-codespostaux/20141106-120608/code_postaux_v201410.csv"),null,";",null,1252),
#"Première ligne comme en-tête" = Table.PromoteHeaders(Source),
#"Type modifié" = Table.TransformColumnTypes(#"Première ligne comme en-tête",{{"code commune
INSEE", Int64.Type}, {"nom de la commune", type text}, {"code postal", Int64.Type}, {"libellé
d'acheminement", type text}})
in
#"Type modifié"
Les commandes du Ruban parlent d'elles-mêmes pour extraire, transformer et 'enrichir les données.
6
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
Supprimons les erreurs éventuelles.
7
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
Chaque instruction réalisée fera l'objet d'un ajout dans la liste des étapes.
La suppression des erreurs a été ajoutées mais avant « Première ligne comme en-tête ».
Essayez de descendre l'étape de suppression.
Impossible une erreur est aussitôt détectée.
Enregistrez et chargez dans Excel. Les 100 premières lignes s'affichent.
Le chargement abouti. Et un compte rendu d'extraction apparaît.
8
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
Vous voici avec la table officielle des codes postaux moins les erreurs que vous pouvez dès maintenant enregistrer
dans SharePoint.
9
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
Ouvrez à nouveau votre fichier Excel et ouvrez la requête correspondante :
Modifiez le type de la colonne Code postal de « Entier » à « Texte » :
10
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
Supprimez les deux premières colonnes code commune INSEE et Nom de la commune.
La requête devient :
let
Source = Csv.Document(Web.Contents("https://www.data.gouv.fr/s/resources/base-officielle-des-codespostaux/20141106-120608/code_postaux_v201410.csv"),null,";",null,1252),
#"Erreurs supprimées" = Table.RemoveRowsWithErrors(Source, {"Column2"}),
#"Première ligne comme en-tête" = Table.PromoteHeaders(#"Erreurs supprimées"),
#"Type modifié" = Table.TransformColumnTypes(#"Première ligne comme en-tête",{{"code commune
INSEE", type text}, {"nom de la commune", type text}, {"code postal", type text}, {"libellé d'acheminement",
type text}}),
#"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"code commune INSEE", "nom de la
commune"})
in
#"Colonnes supprimées"
11
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
Fermez et chargez.
Puis réenregistrez dans SharePoint.
Nous voici avec la table officielle des codes postaux.
12
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Rappelez-vous 60
Il suffit désormais d'ouvrir le classeur stocké dans SharePoint en modification d'actualiser la requête pour que les
données soient rechargées en appliquant systématiquement chaque étape de transformation.
Même si l'intérêt de se connecter à la table officielle des codes postaux est discutable, nous imaginons facilement le
potentiel de gain en efficacité opérationnelle d'un tel outil.
Une nouvelle ère a débuté pour le poweruser proactif.
Ce que nous avons réalisé avec les codes postaux nous pouvons le réiterer avec des bases de données hébergées sur
des serveurs externes ; extraire, transformer et enrichier grâce à Power Query l'information dans des classeurs Excel,
pour les offrir en analyse dans SharePoint à travers des tableaux et des graphiques croisiés dynamiques.
13
[email protected] L'utilisation, la copie, la reproduction des textes de la page ci-dessus, sous quelque forme que ce soit, sont autorisées après accord du
rédacteur susnommé. Merci.
Téléchargement

Rappelez-vous 60 - Synergie - Excel Services Excel