OFFICE
Excel BI powerquery
PRÉREQUIS
Pour cette formation, vous devez être à l’aise dans la création de tableaux avec formules de calculs complexes et maîtriser toute la partie Base de données sous Excel (Mode Tableau, Tris, Filtres, Tableaux Croisés Dynamiques).
OBJECTIFS OPERATIONNELS
Suite à cette formation, vous saurez :
Importer et transformer tout type de données via PowerQuery,
Afficher le résultat d'une requête sous forme de base de données ou de tableau croisé dynamique,
Ajouter des colonnes calculées dans une requête.
PUBLIC
Vous avez besoin de rassembler des données de différents logiciels pour pouvoir faire vos statistiques sous forme de TCD.
Vous disposez de tableaux qui ne sont pas exploitables dans Excel sous forme de bases de données et vous souhaitez les transformer et automatiser sans passer par les macros.
Vous avez des fichiers très lourds et vous souhaitez optimiser l’élaboration de vos analyses statistiques.
CONTENU PÉDAGOGIQUE
Généralités
Connaitre les différentes façons d’accéder à PowerQuery selon la version d'Excel
Présentation de la Business intelligence selon Microsoft
Reconnaitre l’interface PowerQuery
Importation de données simples
Rappels pour nommer des cellules
Rappels sur le mode Tableau d’Excel : intérêts pour PowerQuery et PowerPivot
Apprendre à importer des données issues d’Excel dans PowerQuery
Récupérer les données d’un fichier Texte ou CSV
Les types de données
Afin de pouvoir faire tous les calculs souhaités et de pouvoir relier les données de deux sources, il est important que leur type soit cohérent et correspondant aux types attendus. Vous allez donc dans cette rubrique, apprendre à connaitre et modifier les types de données d’une colonne
Combiner différentes sources de données
Agréger des données en créant une requête intégrant des données de plusieurs feuilles de calculs Excel à partir d’un même classeur
Agréger des données en créant une requête à partir de plusieurs classeurs Excel
Fusionner différentes sources de données
Fusionner des requêtes en spécifiant la relation à mettre en place entre deux sources de données
Développer ou agréger lors d'une fusion
Importation de données à partir d’une base de données
Dans cette rubrique, vous allez apprendre à importer avec une requête tous les fichiers d’un dossier sans devoir les importer un à un. Il peut s’agir de fichiers Texte ou CSV ou Excel
Importation de données à partir d’une base de données
Dans cette rubrique, vous allez apprendre à créer une requête à partir d’une base de données sous format Access ou tout autre système de base de données relationnelle
Création de la requête à partir d’une base de données
Intégration du schéma relationnel dans Power Query
Fusionner deux tables d’une base de données
Regrouper des données en utilisant les calculs de synthèse comme somme,, nombre…
Gérer les connexions
Importer des données en ligne ou à partir d’Outlook
Importer des données à partir de sites Web ou sharepoint
Importer des éléments de votre messagerie ou du calendrier Outlook.
Faire pivoter des données
Dans cette rubrique, vous allez apprendre à transformer un tableau Excel à double entrée en tableaux simple à une entrée afin de pouvoir l’utiliser comme « vraie Base de données ». Cette opération va donc vous permettre de faire pivoter des données en colonnes et les passer en lignes
Importer un fichier TXT non structuré
Supprimer des lignes inutiles et polluantes
Découper un texte en colonne grâce à la spécification d’un séparateur ou d’une largeur fixe
Nettoyer les colonnes en supprimant les espaces ou les caractères non imprimables
Définir une ligne d’intitulés
Supprimer les lignes vides
Remplacer des données spécifiques
Remplir vers le bas une colonne
Réorganiser ses colonnes, renommer une colonne, trier sur plusieurs colonnes
Regrouper les données
Vérifier les doublons
Supprimer les doublons
Regrouper les doublons « partiels »
Fusionner deux requêtes en utilisant les calculs d’agrégat.
Gestion des requêtes dans Excel
Cette rubrique va vous permettre de choisir entre les différents types d’intégration des données dans un fichier Excel et de gérer ensuite ces connexions
Charger une requête dans un tableau Excel
Utiliser le modèle de données
Créer, ajouter, supprimer une connexion
Modifier le paramètre par défaut du chargement
Mettre à jour toutes les données ou une partie des données selon le type d’intégration choisi
Créer un tableau croisé dynamique à partir d’une requête selon le type d’intégration choisi
Dupliquer une requête
Faire référence à une autre requête : créer une nouvelle requête liée à la requête de base
Les formules de calcul dans une requête
Dans cette rubrique vous allez apprendre à utiliser les calculs prédéfinis proposés dans Power Query mais aussi aller plus loin en modifiant des formules ou en intégrant tout type de formules du langage M. Cette rubrique doit vous permettre de comprendre la structure du langage M afin de pouvoir ensuite explorer toutes les possibilités de ce langage.
Ajouter une colonne calculée via l’interface de Power Query
Respecter les règles du langage M
Les formules de calcul sur les données de type Numérique, Texte, Date
Créer des calculs conditionnels
Trouver de l’aide sur les formules