Résumé

Qu'est-ce que Get & Transform?

• Get & Transform est un outil de transformation de données à utiliser dans les progiciels Microsoft Excel et Power BI.
• Les données arrivent souvent dans des formats non structurés, ce qui fait du processus ETL (extraction, transformation et chargement) un processus fastidieux de contournements manuels.
• Get & Transform automatise et accélère le processus de nettoyage et d'organisation de ces données brutes, ce qui aide finalement à la tâche analytique de découverte d'observations et de tendances.
• Voici quelques exemples de fonctionnalités fournies par Get & Transform: Suppression de colonnes, regroupement de données, fractionnement de chaînes en sous-chaînes et ajout de lignes à partir d'une autre table.
• Pour maintenir les flux de travail dans l'univers Excel, Get & Transform est un excellent outil qui peut être facilement expliqué et démontré aux parties prenantes concernées.

Comment puis-je utiliser Get & Transform?

• L'accès dans Excel se fait via le Obtenir et transformer des données section dans le Les données languette. Dans Power BI, il existe dans le Données externes section de la Accueil languette.
• Chargement de CSV: L'importation d'un CSV via Get & Transform permet de le nettoyer et de le rendre `` plus étroit '' ou `` plus large '' pour faciliter le pivotement des données. Ces instructions peuvent être enregistrées puis répétées pour les importations futures.
• Gestion des chaînes de texte: une amélioration significative par rapport à la Texte en colonnes fonctionnalité dans Excel, Get & Transform peut rapidement analyser et séparer le texte combiné et les chaînes numériques dans des colonnes séparées.
• Différentes sources de données: avec un large éventail de fichiers d'entrée acceptés, il est possible de travailler avec des sources disparates tout en conservant une qualité de sortie cohérente et normalisée.
• Personnalisation avec du code: Le langage M est le code fonctionnel utilisé dans Get & Transform, et il est possible d'écrire des requêtes personnalisées pour des requêtes plus personnalisées.

Dans cet âge de lacs de données et les bases de données à l'échelle du pétaoctet, il est choquant de voir à quelle fréquence je reçois encore des données sous forme de fichiers CSV, texte et Excel. Alors que les analyses modernes se concentrent sur les avancées de pointe des algorithmes d'apprentissage automatique, la corvée quotidienne des l'analyse des données est toujours un processus manuel de recherche, de compilation et de lutte contre des types de données disparates.

acheter une carte de crédit en ligne auprès de pirates

Pour l'analyste financier, les données arrivent souvent sous forme de 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 organisées dans une mise en page déroutante ou ne disposent pas de tous les composants requis pour l'analyse. Le temps passé à nettoyer ces données est un temps précieux perdu pour l'analyste, mais cette tâche est parfois acceptée comme un mal nécessaire à tolérer.

Que fait 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és Obtenir et transformer (anciennement Power Query). L'utilisation de sa fonctionnalité intégrée d'extraction, de transformation et de chargement (ETL) permet aux analystes financiers de se lier de manière transparente à leurs sources de données et d'obtenir des informations plus rapidement.

Lorsque nous mettons en amont les données à charger dans Excel ou Power BI, nous devons généralement effectuer certaines transformations des données. Quelques exemples de manipulation de données incluent:

• Suppression de colonnes,
• Filtrer les données,
• Regrouper les données,
• Faire pivoter / décomposer les données,
• Fractionnement 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 est-il intéressant d'apprendre à utiliser Get & Transform? Eh bien, quand je regarde ce pour quoi j'ai personnellement utilisé cette fonctionnalité, cela 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 de fichiers comptables exportés en une mise en page compréhensible
• Chargement direct de millions de lignes de ventes dans Power Pivot
• Regrouper les données quotidiennes en résultats mensuels gérables avant de les importer dans Excel
• Épissage des données d'une autre table en les joignant sur des colonnes correspondantes

En règle générale, 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 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, de la répétabilité et de la distribution de l'analyse. Si mes clients utilisent uniquement 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 se sentira 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. Besoin d'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 vers 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 aider avec 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 le Les données onglet, puis le Obtenir et transformer des données section.

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

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

1. Chargement des fichiers CSV

Pour aider ce didacticiel, j'ai créé quelques exemples de données de vente pour un détaillant fictif qui vend des vêtements et des vêtements d'extérieur. 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.

quels sont les principes gestaltistes de l'organisation perceptive

Comme premier exemple, nous verrons les données présentées sous forme de vidage de données volumineux dans un fichier CSV. Le facteur de complication est que les données sont présentées avec plusieurs colonnes représentant divers magasins. Nous aimerions idéalement 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 tirer parti des capacités de relation possibles dans ces applications. Nous verrons cela jouer plus loin dans la discussion.

Pour le moment, supposons que nous devons considérer 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 à «décomposer» 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, comme 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 il sera revisité plus tard.

Get & Transform ressemble et se comporte de la même manière entre Power BI et Excel pour la plupart. Cependant, dans Excel, après avoir cliqué sur Fermer et charger , il y a une invite supplémentaire. Dans la figure ci-dessous, nous pouvons basculer entre le chargement des données dans:

1. Un tableau dans Excel,
2. Un tableau croisé dynamique créé à partir des données,
3. Un graphique croisé dynamique créé par rapport aux données, ou
4. 'Créez uniquement une connexion.'

De plus, nous avons également la possibilité de décider de Ajouter ces données au modèle de données . Cochez cette case pour charger les données dans un Power Pivot table. Si nous allons analyser les données dans Power Pivot, je vous conseille de choisir Créer uniquement une connexion puis en s'assurant que le Ajouter ces données au modèle de données l'option est sélectionnée. Si les données se trouvent dans la limite de lignes Excel et que nous préférons effectuer notre analyse dans Excel, choisissez simplement Table .

Dans le prochain clip, nous verrons que la raison pour laquelle nous avons formaté les données pour qu'elles soient longues et maigres est que nous sommes en mesure d'analyser les ventes non seulement par magasin, mais aussi par région et é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:

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

Bien 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 analyser sans effort le numéro de compte et le nom dans des champs séparés.

calculateur de taux de salaire au contrat

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 devrait être un nombre, et il crée une étape «Type1 modifié». 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 de comptes 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 être composé de centaines, voire de milliers de comptes. Cette requête de mappage rapide, comme nous l'avons montré, évoluerait à ce niveau sans travail supplémentaire.

3. Utilisation de différentes sources de données

Get & Transform prend en charge de nombreuses sources de données différentes. Bien que n'étant pas une liste exhaustive, voici quelques exemples:

Fichier texte Exceller Facebook Adobe Analytics Google Analytics Salesforce Azur Redshift Étincelle serveur SQL SAP HANA Teradata Google BigQuery

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 aperçus sans une lourde charge de travail. Tout aussi important, il sert de validateur entre des sources de données disparates, garantissant que les résultats finaux ont un niveau normalisé de contrôle de qualité.

4. Personnalisation du code avec le langage M

En arrière-plan, Get & Transform génère du code à chaque fois que nous cliquons sur un bouton de l'outil ou que nous faisons 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 génère automatiquement pour les cas d'utilisation de base. Cependant, pour une manipulation de données plus compliquée, 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 de zéro en étape tables temporaires , ou pour des performances plus compliquées joint .

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'envoyer 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 à déployer vers 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.

quel outil un programmeur utiliserait-il pour visualiser la relation entre les modules ?

Cela dit, je trouve qu'Excel est ce avec quoi la plupart de mes clients sont le plus à l'aise. Excel reste l’outil le plus important de l’arsenal des analystes financiers. En incorporant 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.

Comprendre les bases

Quel est le concept d'ETL?

Extraire, transformer et charger est le processus de transfert de données de différentes sources vers un entrepôt de données centralisé.

À quoi sert Power BI?

Power BI est un logiciel d'analyse commerciale de Microsoft. Il offre des capacités approfondies pour visualiser les données et créer des rapports et des tableaux de bord automatisés.

À quoi sert un lac de données?

Un lac de données est un référentiel unique pour toutes les sources de données au sein d'une organisation. Cela peut inclure des poches de données structurées et non structurées, qui peuvent finalement être utilisées et traitées chaque fois que nécessaire.