Dans ce tutoriel, utilisez l’Éditeur de requête de Power Query pour importer des données à partir d’un fichier Excel local contenant des informations sur le produit et d’un flux OData contenant des informations sur la commande de produits. Effectuez les étapes de transformation et d’agrégation, et combinez les données des deux sources pour créer un rapport Ventes totales par produit et par année .
Pour terminer ce didacticiel, vous avez besoin du classeur Produits . Dans la boîte de dialogue Enregistrer sous, nommez le fichier Products and Orders.xlsx.
Tâche 1 : importer des produits dans un classeur Excel
Dans cette tâche, vous importez des produits à partir du fichier Produits et Orders.xlsx (téléchargé et renommé dans la section précédente) dans un classeur Excel. Ensuite, vous promouvez les lignes en en-têtes de colonne, supprimez certaines colonnes et chargez la requête dans une feuille de calcul.
Étape 1 : Se connecter à un classeur Excel
- Créez un classeur Excel.
- Sélectionnez Données>Obtenir les données>d’un fichier>à partir du classeur.
- Dans la boîte de dialogue Importer des données , recherchez et localisez le fichier Products.xlsx que vous avez téléchargé, puis sélectionnez Ouvrir.
- Dans le volet Navigateur , double-cliquez sur la table Produits . L’Éditeur Power Query s’affiche.
Étape 2 : Examiner les étapes de la requête
Par défaut, Power Query ajoute automatiquement plusieurs étapes pour votre commodité. Pour en savoir plus, examinez chaque étape sous Étapes appliquées dans le volet Paramètres de requête .
- Cliquez avec le bouton droit sur l’étape Source , puis sélectionnez Modifier les paramètres. Cette étape a été créée lors de l’importation du classeur.
- Cliquez avec le bouton droit sur l’étape de navigation , puis sélectionnez Modifier les paramètres. Cette étape a été créée lorsque vous avez sélectionné la table dans la boîte de dialogue Navigation .
- Cliquez avec le bouton droit de la souris sur l’étape Changed Type (Type modifié ), puis sélectionnez Edit Settings (Modifier les paramètres). Cette étape a été créée par Power Query qui déduisait les types de données de chaque colonne. Sélectionnez la flèche vers le bas à droite de la barre de formule pour afficher la formule complète.
Étape 3 : Supprimer les autres colonnes pour afficher uniquement les colonnes qui les intéressent
Au cours de cette étape, vous supprimez toutes les colonnes à l’exception de ProductID,ProductName, CategoryID et QuantityPerUnit.
- Dans Aperçu des données, sélectionnez les colonnes IDPRODUIT, NomProduit, RéfCatégorie et QuantitéParUnité (utilisez Ctrl+Clic ou Maj+Clic).
- Sélectionnez Supprimer les colonnes>Supprimer les autres colonnes.
Étape 4 : Charger la requête produits
Dans cette étape, vous chargez la requête Produits dans une feuille de calcul Excel.
- Sélectionnez Accueil,>Fermer & charger. La requête apparaît dans une nouvelle feuille de calcul Excel.
Résumé : étapes de Power Query créées dans la tâche 1
Lorsque vous effectuez des activités de requête dans Power Query, il crée des étapes de requête et les répertorie dans le volet Paramètres de requête, dans la liste Étapes appliquées. Chaque étape de la requête a une formule Power Query correspondante, appelée également langage « M ». Pour plus d’informations sur les formules Power Query, consultez la documentation Power Query.
| Tâche | Étape de requête | Formule |
|---|---|---|
| Importer un classeur Excel | Source | = Excel.Workbook(File.Contents(« C :\Products and Orders.xlsx »), null, true) |
| Sélectionnez la table Produits | Naviguer | = Source{[Item="Products »,Kind="Table"]}[Data] |
| Power Query détecte automatiquement les types de données de colonne | Type modifié | = Table.TransformColumnTypes( Products_Table,{{"ProductID », Int64.Type}, {"ProductName », type text}, {"SupplierID », Int64.Type}, {"CategoryID », Int64.Type}, {"QuantityPerUnit », type text}, {"UnitPrice », type number}, {"UnitsInStock », Int64.Type}, {"UnitsOnOrder », Int64.Type}, {"ReorderLevel », Int64.Type}, {"Discontinued », type logical}}) |
| Supprimer les autres colonnes pour afficher uniquement les colonnes utiles | Autres colonnes supprimées | = Table.SelectColumns(FirstRowAsHeader,{"ProductID », « ProductName », « CategoryID », « QuantityPerUnit"}) |
Tâche 2 : importer des données de commande à partir d’un flux OData
Dans cette tâche, vous importez des données dans votre classeur Excel à partir de l’exemple de flux Northwind OData à http://services.odata.org/Northwind/Northwind.svc, développez le tableau Order_Details, supprimez des colonnes, calculez un total de ligne, transformez une DateCommande, regroupez des lignes par RéfProduit et Année, renommez la requête et désactivez le téléchargement de requête vers le classeur Excel.
Étape 1 : Connectez-vous à un flux OData
- Sélectionner des données>Obtenir des données>d’autres sources>à partir du flux OData.
- Dans la boîte de dialogue Flux OData, entrez l’URL du flux OData Northwind.
- Sélectionnez OK.
- Dans le volet Navigateur , double-cliquez sur la table Commandes .
Étape 2 : Développer une table Order_Details
Dans cette étape, vous développez la table Order_Details liée à la table Orders pour combiner les colonnes ProductID, UnitPrice et Quantity de la table Order_Details dans la table Orders. L’opération de développement combine les colonnes d’une table liée dans une table d’objet. Lors de l’exécution de la requête, les lignes de la table associée (Order_Details) sont combinées en lignes avec la table primaire (Commandes).
Dans Power Query, une colonne contenant une table associée a la valeur Enregistrement ou Table dans la cellule. Elles sont appelées colonnes structurées. Record (L’enregistrement ) indique un enregistrement associé unique et représente une relation un-à-un avec les données actuelles ou la table primaire. Table indique une table associée et représente une relation un-à-plusieurs avec la table active ou primaire. Une colonne structurée représente une relation dans une source de données qui possède un modèle relationnel. Par exemple, une colonne structurée indique une entité avec une association de clé étrangère dans un flux OData ou une relation de clé étrangère dans une base de données SQL Server.
Une fois la table Order_Details développée, trois nouvelles colonnes et lignes supplémentaires sont ajoutées à la table Commandes , une pour chaque ligne de la table imbriquée ou associée.
Dans Aperçu des données, faites défiler horizontalement jusqu’à la colonne Order_Details .
Dans la colonne Order_Details , sélectionnez l’icône de développement (
).Dans le menu déroulant Développer :
Sélectionnez (Sélectionner toutes les colonnes) pour effacer toutes les colonnes.
Sélectionnez RéfProduit, PrixUnitaire et Quantité.
Sélectionnez OK.
Remarque
Dans Power Query, vous pouvez développer des tables liées à partir d’une colonne et agréger les colonnes de la table liée avant de développer les données de la table d’objet. Pour plus d’informations sur la façon d’effectuer des opérations d’agrégation, voir Agréger les données d’une colonne (Power Query).
Étape 3 : Supprimer les autres colonnes pour afficher uniquement les colonnes qui les intéressent
Au cours de cette étape, vous supprimez toutes les colonnes à l’exception des colonnes DateCommande, IDProduit, PrixUnitaire et Quantité .
Dans Aperçu des données, sélectionnez les colonnes suivantes :
- Sélectionnez la première colonne, OrderID.
- Maj+Cliquez sur la dernière colonne, Expéditeur.
- Appuyez sur la touche Ctrl et cliquez sur les colonnes OrderDate, Order_Details.ProductID, Order_Details.UnitPrice et Order_Details.Quantity.
Cliquez avec le bouton droit sur l’en-tête de colonne sélectionnée, puis sélectionnez Supprimer les autres colonnes.
Étape 4 : Calculer le total de la ligne pour chaque ligne Order_Details
Dans cette étape, vous créez une colonne personnalisée pour calculer le total de chaque ligne Order_Details.
- Dans Aperçu des données, sélectionnez l’icône de tableau (
) dans le coin supérieur gauche de l’aperçu. - Sélectionnez Ajouter une colonne personnalisée.
- Dans la boîte de dialogue Colonne personnalisée , dans la zone de formule de colonne personnalisée , entrez [Order_Details.PrixUnitaire] * [Order_Details.Quantité].
- Dans la zone Nom de la nouvelle colonne , entrez Total de la ligne.
- Sélectionnez OK.
Étape 5 : Transformer une colonne de l’année DateCommande
Dans cette étape, vous transformez la colonne OrderDate pour afficher l’année de la date de commande.
Dans Aperçu des données, cliquez avec le bouton droit sur la colonne DateCommande , puis sélectionnez Transformer>l’année.
Renommez la colonne OrderDate en Year :
- Double-cliquez sur la colonne DateCommande et entrez Année ou
- Cliquez avec le bouton droit sur la colonne DateCommande , sélectionnez Renommer, puis entrez Année.
Étape 6 : grouper les lignes par ProductID et par année
Dans Aperçu des données, sélectionnez Année et Order_Details.IDPRODUIT.
Cliquez avec le bouton droit sur l’un des en-têtes, puis sélectionnez Regrouper par.
Dans la boîte de dialogue Regrouper par :
- Dans la zone de texte Nouveau nom de colonne, entrez Total Sales.
- Dans le menu déroulant Opération, sélectionnez Somme.
- Dans le menu déroulant Colonne, sélectionnez Line Total.
Sélectionnez OK.
Étape 7 : renommer une requête
Avant d’importer les données de ventes dans Excel, renommez la requête :
- Dans le volet Paramètres de requête , dans la zone Nom , entrez Ventes totales.
Résultats : requête finale pour la tâche 2
Après avoir effectué chaque étape, vous avez une requête Ventes totales sur le flux OData Northwind.
Résumé : étapes de Power Query créées dans la tâche 2
Lorsque vous effectuez des activités de requête dans Power Query, il crée des étapes de requête et les répertorie dans le volet Paramètres de requête, dans la liste Étapes appliquées. Chaque étape de la requête a une formule Power Query correspondante, appelée également langage « M ». Pour plus d’informations sur les formules Power Query, consultez la documentation Power Query.
| Tâche | Étape de requête | Formule |
|---|---|---|
| Se connecter à un flux OData | Source | = OData.Feed(« http://services.odata.org/Northwind/Northwind.svc », null, [Implementation="2.0"]) |
| Sélectionner un tableau | Navigation | = Source{[Name="Orders"]}[Data] |
| Développer la table Order_Details | Développer Order_Details | = Table.ExpandTableColumn(Orders, « Order_Details », {"ProductID », « UnitPrice », « Quantity"}, {"Order_Details.ProductID », « Order_Details.UnitPrice », « Order_Details.Quantity"}) |
| Supprimer les autres colonnes pour afficher uniquement les colonnes utiles | RemovedColumns | = Table.RemoveColumns(#"Expand Order_Details »,{"OrderID », « CustomerID », « EmployeeID », « RequiredDate », « ShippedDate », « ShipVia », « Freight », « ShipName », « ShipAddress », « ShipCity », « ShipRegion », « ShipPostalCode », « ShipCountry », « Customer », « Employee », « Shipper"}) |
| Calculer le total de chaque ligne Order_Details | Personnalisation ajoutée |
= Table.AddColumn(RemovedColumns, « Custom », each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details », « Line Total », each [Order_Details.UnitPrice] * [Order_Details.Quantity]) |
| Remplacez par un nom plus significatif, Lne Total | Colonnes renommées | = Table.RenameColumns(InsertedCustom,{{"Custom », « Line Total"}}) |
| Transformer la colonne OrderDate pour afficher l’année | Année extraite | = Table.TransformColumns(#"Grouped Rows »,{{"Year », Date.Year, Int64.Type}}) |
| Choisissez des noms plus significatifs, OrderDate et Year |
Colonnes renommées 1 |
Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}}) |
| Regrouper les lignes par ProductID et Year | GroupedRows | = Table.Group(RenamedColumns1, {"Year », « Order_Details.ProductID"}, {{"Total Total », each List.Sum([Line Total]), type number}}) |
Tâche 3 : combiner les requêtes Products et Total Sales
Power Query vous permet de combiner plusieurs requêtes en les fusionnant ou en les ajoutant. Vous pouvez effectuer l’opération de fusion sur n’importe quelle requête Power Query avec une forme tabulaire, quelle que soit la source de données. Pour plus d’informations sur la combinaison de sources de données, consultez Combiner plusieurs requêtes (Power Query).
Dans cette tâche, vous combinez les requêtes Produits et Total des ventes à l’aide d’une opération de fusionet d’extension, puis vous chargez la requête Total des ventes par produit dans le modèle de données Excel.
Étape 1 : fusionner ProductID dans une requête Ventes totales
Dans le classeur Excel, accédez à la requête Produits dans l’onglet Feuille de calcul Produits .
Sélectionnez une cellule dans la requête, puis sélectionnezFusion de requêtes>.
Dans la boîte de dialogue Fusionner , sélectionnez Produits comme table principale et Ventes totales comme requête secondaire ou associée à fusionner. Le total des ventes devient une nouvelle colonne structurée avec une icône de développement.
Pour associer Total Sales à Products via la colonne ProductID, sélectionnez la colonne ProductID dans la table Products, et la colonne Order_Details.ProductID dans la table Total Sales.
Dans la boîte de dialogue Niveaux de confidentialité :
- Sélectionnez le niveau de confidentialité Organisationnel pour les deux sources de données.
- Sélectionnez Enregistrer.
Sélectionnez OK.
Remarque
Les niveaux de confidentialité empêchent les utilisateurs de combiner par inadvertance les données de plusieurs sources de données (potentiellement privées ou organisationnelles). Selon la requête, les utilisateurs pourraient envoyer par inadvertance des données de la source de données privée à une autre source de données pouvant être nuisible. Power Query analyse chaque source de données et classe celle-ci au niveau de confidentialité défini : Public, Organisationnel et Privé. Pour plus d’informations sur les niveaux de confidentialité, voir Définir des niveaux de confidentialité (Power Query).
Résultat
L’opération de fusion crée une requête. Le résultat de la requête contient toutes les colonnes de la table primaire (Produits) et une seule colonne structurée de table vers la table associée (Total des ventes). Sélectionnez l’icône Développer pour ajouter de nouvelles colonnes à la table primaire à partir de la table secondaire ou liée.
Étape 2 : Développer une colonne fusionnée
Dans cette étape, vous développez la colonne fusionnée avec le nom NewColumn pour créer deux colonnes dans la requête Produits : Année et Total des ventes.
Dans Aperçu des données, sélectionnez Développer l’icône (
) en regard de NewColumn.Dans la liste déroulante Développer :
- Sélectionnez (Sélectionner toutes les colonnes) pour effacer toutes les colonnes.
- Sélectionnez Année et total des ventes.
- Sélectionnez OK.
Renommez ces deux colonnes Year et Total Sales.
Pour savoir quels produits et quelles années les produits ont obtenu le volume de ventes le plus élevé, sélectionnez Trier par ordre décroissant par ventes totales.
Renommez la requête Total Sales per Product.
Résultat
Étape 3 : Charger une requête Ventes totales par produit dans un modèle de données Excel
Dans cette étape, vous chargez une requête dans un modèle de données Excel, de sorte que vous pouvez créer un rapport connecté au résultat de la requête. Une fois que vous avez chargé des données dans le modèle de données Excel, vous pouvez utiliser Power Pivot pour approfondir votre analyse des données.
- Sélectionnez Accueil,>Fermer & charger.
- Dans la boîte de dialogue Importer des données , assurez-vous de sélectionner Ajouter ces données au modèle de données. Pour plus d’informations sur l’utilisation de cette boîte de dialogue, sélectionnez le point d’interrogation ( ?).
Résultat
Vous disposez d’une requête Ventes totales par produit qui combine les données du fichier Products.xlsx et du flux OData Northwind. Cette requête est appliquée à un modèle Power Pivot. En outre, les modifications apportées à la requête modifient et actualisent la table résultante dans le modèle de données.
Résumé : étapes de Power Query créées dans la tâche 3
Au fur et à mesure que vous effectuez des activités de fusion de requêtes dans Power Query, les étapes de requête sont créées et répertoriées dans le volet Paramètres de requête, dans la liste Étapes appliquées. Chaque étape de la requête a une formule Power Query correspondante, appelée également langage « M ». Pour plus d’informations sur les formules Power Query, consultez la documentation Power Query.
| Tâche | Étape de requête | Formule |
|---|---|---|
| Fusionner ProductID dans la requête Total Sales | Source (source de données pour l’opération de fusion) | = Table.NestedJoin(Products, {"ProductID"}, #"Total Sales », {"Order_Details.ProductID"}, « Total Sales », JoinKind.LeftOuter) |
| Développer une colonne de fusion | Expanded Total Sales | = Table.ExpandTableColumn(Source, « Total Sales », {"Year », « Total Sales"}, {"Total Sales.Year », « Total Sales.Total Sales"}) |
| Renommer deux colonnes | Colonnes renommées | = Table.RenameColumns(#"Expanded Total Sales »,{{"Total Sales.Year », « Year"}, {"Total Sales.Total Sales », « Total Sales"}}) |
| Trier le total des ventes par ordre croissant | Lignes triées | = Table.Sort(#"Renamed Columns »,{{"Total Sales », Order.Ascending}}) |