Apprendre à combiner plusieurs sources de données (Power Query)

Dans cette tâche, vous importez des produits à partir du fichier Products and Orders.xlsx (téléchargé et renommé ci-dessus) dans un classeur Excel, vous allez promouvoir des lignes en en-têtes de colonne, supprimer certaines colonnes et charger la requête dans une feuille de calcul.

Étape 1 : se connecter à un classeur Excel

1. Créez un classeur Excel.

2. Sélectionnez Data > Get Data > From File > From Workbook.

3. Dans la boîte de dialogue Importer des données, recherchez et recherchez le fichier Products.xlsx que vous avez téléchargé, puis sélectionnez Ouvrir.

4. Dans le volet Navigateur , double-cliquez sur la table Products . Power Éditeur de requête s’affiche.

Étape 2 : Examiner les étapes de requête

Par défaut, Power Query ajoute automatiquement plusieurs étapes pour vous faciliter la tâche. Examinez chaque étape sous Étapes appliquées dans le volet Paramètres de requête pour en savoir plus.

1. Cliquez avec le bouton droit sur l’étape Source , puis sélectionnez Modifier les paramètres. Cette étape a été créée lorsque vous avez importé le classeur.

2. Cliquez avec le bouton droit sur l’étape 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 .

3. Cliquez avec le bouton droit sur l’étape Type modifié, puis sélectionnez Modifier les paramètres. Cette étape a été créée par Power Query qui a déduit 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 utiles

Dans cette étape, vous supprimez toutes les colonnes à l’exception de ProductID, ProductName, CategoryID et QuantityPerUnit.

1. Dans Aperçu des données, sélectionnez les colonnes ProductID, ProductName, CategoryID et QuantityPerUnit (utilisez Ctrl+Clic ou Maj+Clic).

2. Sélectionnez Supprimer les colonnes > Supprimer d’autres colonnes.

   

Étape 4 : Charger la requête products

Dans cette étape, vous chargez la requête Products 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é : Power Query étapes créées dans la tâche 1

Lorsque vous effectuez des activités de requête dans Power Query, les étapes de requête sont créées et répertoriées dans le volet Paramètres de la 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, voir Créer des formules Power Query dans Excel.

Tâche	Étape de requête	Formule
Importer un classeur Excel	Source	= Excel.Workbook(File.Contents(« C :\Products and Orders.xlsx »), null, true)
Sélectionner la table Products	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"})

Dans cette tâche, vous importez des données dans votre classeur Excel à partir de l’exemple de flux OData Northwind à http://services.odata.org/Northwind/Northwind.svc,développez le tableau Order_Details, supprimez des colonnes, calculez un total de ligne, transformez un OrderDate, regroupez les lignes par ProductID et Year, renommez la requête et désactivez le téléchargement de la requête dans le classeur Excel.

Étape 1 : Se connecter à un flux OData

1. Sélectionnez Données > Obtenir des données > à partir d’autres sources > à partir du flux OData.

2. Dans la boîte de dialogue Flux OData, entrez l’URL du flux OData Northwind.

3. Sélectionnez OK.

4. 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. Lorsque la requête s’exécute, les lignes de la table associée (Order_Details) sont combinées en lignes avec la table primaire (Orders).

Dans Power Query, une colonne contenant une table associée a la valeur Record ou Table dans la cellule. Ces colonnes sont appelées colonnes structurées. Record indique un enregistrement associé unique et représente unerelation 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 actuelle ou principale. Une colonne structurée représente une relation dans une source de données qui a 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 que vous avez développé la table Order_Details, trois nouvelles colonnes et des lignes supplémentaires sont ajoutées à la table Orders, une pour chaque ligne dans la table imbriquée ou liée.

1. Dans Aperçu des données, faites défiler horizontalement jusqu’à la colonne Order_Details .

2. Dans la colonne Order_Details , sélectionnez l’icône développer ().

3. Dans le menu déroulant Développer :

   1. Sélectionnez (Sélectionner toutes les colonnes) pour effacer toutes les colonnes.

   2. Sélectionnez ProductID, UnitPrice et Quantity.

   3. 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 dans la table objet. Pour plus d’informations sur l’exécution des opérations d’agrégation, voir Agréger les données d’une colonne.

Étape 3 : supprimer les autres colonnes pour afficher uniquement les colonnes utiles

Dans cette étape, vous supprimez toutes les colonnes à l’exception de OrderDate, ProductID, UnitPrice et Quantity. 

1. Dans Aperçu des données, sélectionnez les colonnes suivantes : 

   1. Sélectionnez la première colonne, OrderID.

   2. Maj+Cliquez sur la dernière colonne, Expéditeur.

   3. Appuyez sur la touche Ctrl et cliquez sur les colonnes OrderDate, Order_Details.ProductID, Order_Details.UnitPrice et Order_Details.Quantity.

2. Cliquez avec le bouton droit sur un en-tête de colonne sélectionné, puis sélectionnez Supprimer d’autres colonnes.

Étape 4 : calculer le total de chaque ligne Order_Details

Dans cette étape, vous créez une colonne personnalisée pour calculer le total de chaque ligne Order_Details.

1. Dans Aperçu des données, sélectionnez l’icône de table () en haut à gauche de l’aperçu.

2. Cliquez sur Ajouter une colonne personnalisée.

3. Dans la boîte de dialogue Colonne personnalisée , dans la zone Formule de colonne personnalisée , entrez [Order_Details.UnitPrice] * [Order_Details.Quantity].

4. Dans la zone Nouveau nom de colonne , entrez Total de ligne.

5. Sélectionnez OK.

Étape 5 : transformer une colonne d’année OrderDate

Dans cette étape, vous transformez la colonne OrderDate pour afficher l’année de la date de commande.

1. Dans Aperçu des données, cliquez avec le bouton droit sur la colonne OrderDate , puis sélectionnez Transformer > Year.

2. Renommez la colonne OrderDate en Year :

   1. Double-cliquez sur la colonne OrderDate, puis entrez Year ou

   2. Right-Click dans la colonne OrderDate , sélectionnez Renommer, puis entrez Année.

Étape 6 : regrouper les lignes par ProductID et Year

1. Dans Préversion des données, sélectionnez Year et Order_Details.ProductID.

2. Right-Click l’un des en-têtes, puis sélectionnez Regrouper par.

3. Dans la boîte de dialogue Regrouper par :

   1. Dans la zone de texte Nouveau nom de colonne, entrez Total Sales.

   2. Dans le menu déroulant Opération, sélectionnez Somme.

   3. Dans le menu déroulant Colonne, sélectionnez Line Total.

4. 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 la requête , dans la zone Nom, entrez Total Sales.

Résultats : Requête finale pour la tâche 2

Après avoir effectué chaque étape, vous aurez une requête Ventes totales sur le flux de données OData Northwind.

Résumé : Power Query étapes créées dans la tâche 2 

Lorsque vous effectuez des activités de requête dans Power Query, les étapes de requête sont créées et répertoriées dans le volet Paramètres de la 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 En savoir plus sur les formules 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 explicite, 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(#"Groupd Rows »,{{"Year », Date.Year, Int64.Type}})
Choisissez  noms plus explicites, 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 Sales », each List.Sum([Line Total]), type number}})

Power Query vous permet de combiner plusieurs requêtes en fusionnant ou en ajoutant celles-ci. L’opération de fusion est exécutée sur n’importe quelle requête Power Query avec une forme tabulaire, indépendamment de la source de données dont proviennent les données. Pour plus d’informations sur la combinaison de sources de données, voir Combiner plusieurs requêtes.

Dans cette tâche, vous combinez les requêtes Products et Total Sales à l’aide d’une requête fusionner et d’une opération Développer, puis vous chargez la requête Total Sales per Product dans le modèle de données Excel.

Étape 1 : fusionner ProductID dans une requête Total Sales

1. Dans le classeur Excel, accédez à la requête Products sous l’onglet De la feuille de calcul Produits .

2. Sélectionnez une cellule dans la requête, puis sélectionnez Requête > Fusionner.

3. Dans la boîte de dialogue Fusionner , sélectionnez Produits comme table principale, puis sélectionnez Total Sales comme requête secondaire ou associée à fusionner. Total Sales devient une nouvelle colonne structurée avec une icône développer.

4. 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.

5. Dans la boîte de dialogue Niveaux de confidentialité :

   1. Sélectionnez le niveau de confidentialité Organisationnel pour les deux sources de données.

   2. Sélectionnez Enregistrer.

6. Sélectionnez OK.

   Note de sécurité : 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é, consultez Définir des niveaux de confidentialité.

   

Result (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 (Products) et une seule colonne structurée table pour la table associée (Total Sales). Sélectionnez l’icône Développer pour ajouter de nouvelles colonnes à la table primaire à partir de la table secondaire ou associé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 Products : Year et Total Sales.

1. Dans Aperçu des données, sélectionnez Développer icône () en regard de NewColumn.

2. Dans la liste déroulante Développer :

   1. Sélectionnez (Sélectionner toutes les colonnes) pour effacer toutes les colonnes.

   2. Sélectionnez Année et total des ventes.

   3. Sélectionnez OK.

3. Renommez ces deux colonnes Year et Total Sales.

4. Pour savoir quels produits et quelles années les produits ont obtenu le volume de ventes le plus élevé, sélectionnez Trier décroissant par total des ventes.

5. Renommez la requête Total Sales per Product.

Result (Résultat)

Étape 3 : charger une requête Total Sales per Product dans un modèle de données Excel

Dans cette étape, vous chargez une requête dans un modèle de données Excel, afin de générer un rapport connecté au résultat de la requête. Après avoir chargé des données dans le modèle de données Excel, vous pouvez utiliser Power Pivot pour approfondir votre analyse des données.

1. Sélectionnez Accueil > Fermer & Charger.

2. Dans la boîte de dialogue Importer des données , veillez à 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 ( ?).

Result (Résultat)

Vous disposez d’une requête Total Sales per Product 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é : Power Query étapes créées dans la tâche 3

Lorsque vous effectuez des activités de requête de fusion dans Power Query, les étapes de requête sont créées et répertoriées dans le volet Paramètres de la 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 En savoir plus sur les formules 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	Nombre total de ventes étendu	= 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 dans l’ordre croissant	Lignes triées	= Table.Sort(#"Renamed Columns »,{{"Total Sales », Order.Ascending}})