Excel – Atelier – Extraire et transformer vos données avec Power Query

Objectifs pédagogiques

• Connecter un classeur à différentes sources de données externes
• Transformer et filtrer des données
• Combiner des sources de données par fusion ou par ajout
• Pivoter ou dépivoter des colonnes
• Identifier l’éditeur avancé et le langage M.

Modalités et moyens pédagogiques

Formation délivrée en présentiel ou distanciel* (e-learning, classe virtuelle, présentiel à distance).

Le formateur alterne entre méthode** démonstrative, interrogative et active (via des travaux pratiques et/ou des mises en situation).

Variables suivant les formations, les moyens pédagogiques mis en oeuvre sont :

• Ordinateurs PC, connexion internet fibre, tableau blanc ou paperboard, vidéoprojecteur ou écran tactile interactif (pour le distanciel)
• Environnements de formation installés sur les postes de travail ou en ligne
• Supports de cours et exercices

En cas de formation intra sur site externe à Did´s Dev, le client s’assure et s’engage également à avoir toutes les ressources matérielles pédagogiques nécessaires (équipements informatique…)

au bon déroulement de l’action de formation visée conformément aux prérequis indiqués dans le programme de formation communiqué.

Niveau requis

Avoir des bonnes connaissances d’Excel pour des utilisateurs réalisant au quotidien

des transformations et des manipulations de données, en particulier les tableaux croisés dynamiques et les listes.

Public concerné

Utilisateurs souhaitant automatiser le processus de préparation de sources données en vue de leur analyse dans des tableaux de bords et des rapports.

Cette formation :

• Est animée par un consultant-formateur dont les compétences techniques, professionnelles et pédagogiques ont été validées par des diplômes et/ou testées et approuvées par l’éditeur et/ou par Did´s Dev
• Bénéficie d’un suivi de son exécution par une feuille de présence émargée par demi-journée par les stagiaires et le formateur.

Programme

1ère demi-journée

Présentation de Power Query

• Qu’est-ce qu’un ETL (Extract, Transform and Load) ?
• Pourquoi utiliser Power Query ?
• Découvrir l’onglet Données et le groupe Récupérer et transformer des données
• Présentation de l’interface de Power Query
• Le concept des étapes appliquées

Obtenir des données à partir de différentes sources

• Créer des requêtes à partir de fichiers externes
  • Fichiers plats (TXT, CSV)
  • Fichiers Excel
  • Cloud et Web (OneDrive, SharePoint, site Internet)
• Créer une requête à partir d’un tableau interne
• Actualiser les données

Exemple de travaux pratiques (à titre indicatif)

• Créer différentes requêtes provenant de sources de données diverses (ERP, base de données)

2ème demi-journée

Transformer et nettoyer les données

• Définir la ligne d’en-tête
• Vérifier et appliquer le bon type de données
• Choisir les données utiles
  • Choisir les colonnes
  • Conserver / supprimer les lignes
  • Filtrer des données
  • Supprimer les doublons
  • Supprimer les erreurs
• Remplacer les valeurs Null
• Fractionner / concaténer des colonnes
• Transformer les colonnes
  • Supprimer les espaces
  • Nettoyer
  • Changer la casse
• Transformer les données

• A partir de colonnes de type texte
  • A partir de colonnes de type numérique
  • A partir de colonnes de type chronologique
• Les transformations remarquables
  • Pivoter une table
  • Dépivoter les colonnes

Exemples de travaux pratiques (à titre indicatif)

• Créer des requêtes complexes en utilisant les outils de transformation de requêtes
• Transformer un tableau à double entrée en liste

3ème demi-journée

Ajouter des colonnes calculées et regrouper des lignes

• Création de colonnes
  • Personnalisées pour insérer des calculs
  • Conditionnelles
  • A partir d’exemple
  • D’index
• Regroupement de lignes
  • Choisir les colonnes à regrouper
  • Choisir les agrégats à appliquer
• Utiliser une fonction personnalisée

Exemples de travaux pratiques (à titre indicatif)

• Concevoir des calculs au sein des requêtes permettant de réaliser des statistiques
• Utiliser les fonctionnalités de calculs par l’exemple pour composer un mail, un trigramme

4ème demi-journée

Combiner des requêtes

• Combiner une requête par fusion
  • Choisir le type de jointure
• Combiner des requêtes par ajout
  • Ajouter une requête à la requête du classeur
  • Compiler tous les fichiers d’un dossier
• Dupliquer une requête
• Créer une référence de requête

Découvertes et bonnes pratiques

• Nommer les étapes
• Ajouter une description à une étape
• Configurer l’affichage pour discerner la qualité et le profil de la colonne
• Afficher la fenêtre de dépendances de la requête
• Comprendre le potentiel du langage M
• Utiliser l’éditeur avancé

Exemples de travaux pratiques (à titre indicatif)

• Créer des requêtes combinées pour réaliser des fusions et des ajouts afin d’obtenir une seule requête à partir de sources multiples
• Calculer des anciennetés

Le contenu de ce programme peut faire l’objet d’adaptation selon les niveaux, prérequis et besoins des apprenants.

Modalités d’évaluation des acquis

• En cours de formation, par des exercices pratiques
• Et, en fin de formation, par un questionnaire d’auto-évaluation ou une certification (Did´s Dev ou éditeur)