Explorer la fonctionnalité Get & Transform d'Excel

Résumé

À l'ère des lacs de données et des bases de données à l'échelle du pétaoctet, il est choquant de constater à quelle fréquence je reçois encore des données sous forme de fichiers CSV, texte et Excel. Alors que l'analyse moderne se concentre sur les avancées de pointe des algorithmes d'apprentissage automatique, la corvée quotidienne de l'analyse des données reste un processus manuel de recherche, de compilation et de traitement de types de données disparates.

Pour l'analyste financier, les données arrivent souvent sous la forme d'une feuille de calcul Excel, mais tout aussi souvent, il s'agit d'un vidage de données dans un CSV ou d'une requête dans une base de données SQL. Parfois, les données sont disposées dans une disposition déroutante ou n'ont pas tous les composants requis pour l'analyse. Le temps passé à nettoyer ces données est un temps précieux perdu pour l'analyste, mais parfois cette tâche est acceptée comme un mal nécessaire à tolérer.

Que font Get & Transform ?

Une solution à ce problème courant est en fait assez accessible : Excel et Power BI disposent d'un ensemble complet d'outils de transformation de données que peu d'utilisateurs connaissent, nommé Get & Transform (anciennement connu sous le nom de Power Query). L'utilisation de sa fonctionnalité intégrée d'extraction, de transformation et de chargement (ETL) permet aux analystes financiers de se connecter de manière transparente à leurs sources de données et d'obtenir des informations plus rapidement.

Au fur et à mesure que nous chargeons des données dans Excel ou Power BI, nous devons généralement effectuer certaines transformations sur les données. Voici quelques exemples de manipulation de données :

• Suppression de colonnes,
• Filtrer les données,
• Regroupement des données,
• Pivoter/dépivoter les données,
• Découper des chaînes en sous-chaînes,
• Extraction de mots-clés à partir de chaînes,
• Ajout de lignes d'une autre table, et
• Joindre deux tables de dimension.

Dans le diagramme ci-dessous, nous voyons que Get & Transform remplit ce rôle fastidieux de pré-traitement des données avant leur chargement.

Pourquoi devriez-vous utiliser Get & Transform ?

Pourquoi vaut-il la peine d'apprendre à utiliser Get & Transform ? Eh bien, quand je regarde ce pour quoi j'ai personnellement utilisé cette fonctionnalité, elle m'a offert un ensemble d'outils malléables pour :

• Chargement d'un dossier entier de fichiers texte dans une seule table de données
• Conversion des fichiers comptables exportés en une mise en page digeste
• Chargement direct de millions de lignes de vente dans Power Pivot
• Regrouper les données quotidiennes en résultats mensuels gérables avant de les importer dans Excel
• Fusionner les données d'une autre table en joignant les colonnes correspondantes

Généralement, lorsque je reçois de nouvelles données, je les explore à l'aide de Get & Transform avant de les charger dans Power Pivot. Cela me permet de voir quelles transformations pourraient être nécessaires et d'effectuer rapidement quelques pivots et regroupements sur les données pour formuler un cadre d'analyse. Dans de nombreux cas, à ce stade, je constaterai que j'ai besoin de plus de données ou qu'il y a des problèmes de données. En utilisant une plate-forme basée sur Excel, je peux rapidement itérer avec ma source de données pour trouver ces anomalies de données.

En fin de compte, la décision de rester dans Excel ou de déplacer l'analyse des données vers une autre plate-forme dépendra de l'audience ainsi que de la répétabilité et de la distribution de l'analyse. Si mes clients n'utilisent qu'Excel, j'utiliserai presque toujours Get & Transform pour charger les données, Power Pivot pour effectuer l'analyse et Excel pour produire les tableaux croisés dynamiques et les graphiques. Pour le client, cela semblera transparent car tout est hébergé dans Excel.

Cependant, si mon client :

1. Veut utiliser un autre outil de visualisation,
2. A plusieurs utilisateurs qui actualiseront les données, ou
3. Doit utiliser des modèles d'apprentissage automatique,

Ensuite, j'utiliserai Get & Transform uniquement pour l'exploration initiale des données, puis je déplacerai le gros du travail dans R.

Comment accéder à Get & Transform dans Excel ou Power BI

Dans les versions précédentes d'Excel, Power Query était un complément qui pouvait être installé pour faciliter les fonctions ETL. Cependant, dans Excel 2016 et Power BI, ces outils sont plus étroitement intégrés. Dans Excel 2016, ils sont accessibles via l'onglet Données , puis la section Obtenir et transformer les données .

Dans Power BI, la fonctionnalité existe dans l'onglet Accueil , dans la section Données externes .

Dans cet article, mes exemples se déroulent dans Power BI, mais l'interface est quasiment identique à celle d'Excel. Je soulignerai les différences lorsqu'elles se présenteront afin que le didacticiel ait du sens pour les deux types d'utilisateurs.

1. Chargement des fichiers CSV

Pour faciliter ce didacticiel, j'ai créé quelques exemples de données de vente pour un détaillant fictif qui vend des équipements et des vêtements de plein air. Dans chacun de ces exemples, les données seront produites de différentes manières pour démontrer des méthodes réalistes de vidage de données.

Comme premier exemple, nous verrons les données présentées sous la forme d'un gros vidage de données dans un fichier CSV. Le facteur de complication est que les données sont présentées avec plusieurs colonnes représentant divers magasins. Idéalement, nous aimerions importer et transformer les données en une mise en page plus utilisable.

Vous trouverez ci-dessous une capture d'écran de ce à quoi ressemble le CSV brut :

Pourquoi voudrions-nous changer cela? Pour profiter des capacités de relation qui sont possibles dans ces applications. Nous verrons ce jeu plus loin dans la discussion.

Pour l'instant, supposons que nous ayons besoin de voir les données comme une structure « plus étroite et plus haute », plutôt que comme une structure « plus large et plus courte ». La première étape consiste à charger le CSV ; ensuite, nous commencerons à "unpivoter" les données.

Comme vous pouvez le voir, la structure finale des données est plus étroite que les données initiales, et beaucoup plus longue. Un autre point est que, lorsque nous cliquons sur différentes actions, l'outil sur le côté droit génère une liste d'étapes appliquées utilisées pour construire la requête. Il est important de comprendre que cela se passe en arrière-plan, car cela sera revu plus tard.

Get & Transform ressemble et se comporte de manière similaire entre Power BI et Excel pour la plupart. Cependant, dans Excel, après avoir cliqué sur Fermer et charger , il existe une invite supplémentaire. Dans la figure ci-dessous, nous pouvons basculer entre si nous souhaitons charger les données dans :

1. Un tableau sous Excel,
2. Un tableau croisé dynamique créé par rapport aux données,
3. Un graphique croisé dynamique créé par rapport aux données, ou
4. "Créer uniquement une connexion."

De plus, nous avons également la possibilité d' ajouter ou non ces données au modèle de données . Cocher cette case charge les données dans un tableau Power Pivot. Si nous allons analyser les données dans Power Pivot, je vous conseille de choisir uniquement Créer une connexion , puis de vous assurer que l'option Ajouter ces données au modèle de données est sélectionnée. Si les données se situent dans la limite de lignes Excel et que nous préférons effectuer notre analyse dans Excel, choisissez simplement Table .

Dans le clip suivant, nous verrons que la raison pour laquelle nous avons formaté les données pour qu'elles soient longues et maigres est de pouvoir analyser les ventes non seulement par magasin, mais aussi par région et par état. Pour accomplir cette tâche, nous allons importer une table qui mappe chaque magasin à une région et à un état. Nous verrons ci-dessous que nous pouvons créer rapidement des rapports qui montrent les ventes par ces différents regroupements.

Vous pouvez imaginer comment ce type de capacité de transformation de données dans Excel ou Power BI peut être puissamment appliqué à tous les cas où nous avons des regroupements dynamiques de données, tels que :

• Regrouper les données quotidiennes en semaines, mois et trimestres ;
• Regrouper le personnel de vente en départements et régions ; ou
• Mappage des SKU aux types de produits.

Alors que cet article traite des fichiers CSV et d'autres fichiers Excel, Get & Transform aborde un large éventail de types de données. Une fois qu'une requête est créée, elle peut être actualisée au fil du temps à mesure que les données changent.

2. Gestion des chaînes de texte

Afin de démontrer la capacité de Get & Transform à manipuler des chaînes, j'ai créé un autre ensemble de données qui imite un fichier texte montrant les transactions comptables du grand livre général (GL) d'une entreprise.

Remarquez comment le numéro de compte et le nom apparaissent dans la même chaîne ? Dans Power BI, nous pouvons facilement analyser le numéro et le nom du compte dans des champs distincts.

Dans cette vidéo, vous pouvez voir qu'après avoir divisé la colonne, l'outil a deviné que le nouveau côté gauche du champ Compte devait être un nombre, et il crée une étape "Changed Type1". Puisque nous voulons finalement que ce champ soit une chaîne, nous pouvons continuer et supprimer l'étape manuellement sous les étapes appliquées.

Ensuite, nous prenons les mêmes données et créons un plan comptable avec des mappages aux catégories de comptes.

Pourquoi passerions-nous par toutes ces étapes pour mapper quelques numéros de compte ? Un vrai grand livre général peut contenir des centaines, voire des milliers de comptes. Cette requête de mappage rapide, comme nous l'avons montré, évoluerait jusqu'à ce niveau sans travail supplémentaire.

3. Travailler avec différentes sources de données

Get & Transform prend en charge de nombreuses sources de données différentes. Sans être une liste exhaustive, voici quelques exemples :

Personnellement, je n'ai essayé qu'environ la moitié des connexions de la liste ci-dessus. Chacun des connecteurs que j'ai utilisés a été assez robuste ; Je suis passé de données brutes à des idées sans une charge de travail fastidieuse. Tout aussi important, il sert de validateur entre des sources de données disparates, garantissant que les résultats finaux ont un niveau de contrôle de qualité normalisé.

4. Personnaliser le code avec le langage M

En arrière-plan, Get & Transform génère du code chaque fois que nous cliquons sur un bouton dans l'outil ou effectuons une sélection. Vous trouverez ci-dessous un exemple de la manière dont vous accéderiez au code de la requête de mappage de compte que nous avons créée :

Le code utilise un langage fonctionnel nommé M, qui se génère automatiquement pour les cas d'utilisation de base. Cependant, pour des manipulations de données plus compliquées, nous pouvons éditer et écrire notre propre code. Dans la plupart des cas, je n'apporterai que des modifications mineures à ce code. Dans les transformations plus compliquées, je peux écrire la plupart du code à partir de zéro pour créer des tables temporaires ou pour effectuer des jointures plus compliquées.

Les limites de Get & Transform

Excel a tendance à atteindre ses limites lorsque vous essayez d'exporter plus d'un million de lignes. Dans les cas où j'ai transformé des millions de lignes avec Get & Transform, la seule façon d'expédier des lignes non groupées est via des hacks fastidieux ou des solutions de contournement. J'ai également constaté que les requêtes Get & Transform peuvent être instables pour être déployées sur plusieurs utilisateurs, en particulier si vous utilisez plusieurs sources de données et jointures. Dans ces cas, j'utiliserai toujours R pour déployer la gestion des données duplicables. Enfin, Excel n'est pas conçu pour une modélisation de données plus avancée. Vous pouvez effectuer des régressions linéaires assez rapidement, mais au-delà, vous devrez utiliser une plateforme plus rigoureuse.

Cela dit, je trouve qu'Excel est ce avec quoi la plupart de mes clients sont les plus à l'aise. Excel reste l'outil le plus important dans l'arsenal d'un analyste financier. En intégrant la fonctionnalité Get & Transform, Excel et Power BI deviennent encore plus puissants grâce à la gamme de sources de données qu'ils peuvent accepter.