samedi 14 juillet 2018

Power Query : Ligne décalées cas 02 – Produits, Quantités et Prix de ventes (méthode 01)


Bonjour ! Et bienvenu de ce nouvel épisode des tutoriels Power Query

Lors d'un précédent billet intitulé  "Power Query ; Lignes décalées cas 01 – Produits et Quantité", j'ai présenté une démonstration qui avait pour objectif de passer d'un tableau d'une colonne contenant en alternance des produits puis leurs quantités vers un tableau de deux colonnes avec les produits dans la première et leurs quantité respectives dans la seconde.

Aujourd'hui je vous propose de découvrir comment faire si le tableau contient non pas uniquement le produit suivi de sa quantité mais le produit suivi de sa quantité puis de son prix de vente.

Donc on va passer d'un tableau d'une colonne qui contient en alternance le nom du produit sa quantité puis son prix de vente vers un tableau de trois colonnes avec le nom du produit dans la première, sa quantité dans la seconde et son prix de vente dans la troisième.

Objectif

L'objectif de cette transformation est de passer d'un tableau d'une colonne contenant en alternance des produits puis leurs quantités puis leurs prix de ventes vers un tableau de trois colonnes tel que la première colonne contiendra la liste des produits alors que la seconde contiendra leurs quantités et la troisième leurs prix de ventes.


À la fin de cette démonstration vous aurez découvert les fonctionnalités Power Query suivantes
  • Créer une colonne d'index
  • Créer une colonne modulo
  • Créer une requête par référence à une autre
  • Créer une requête par fusion de requêtes
  • Renommer une requête
  • Renommer une colonne
  • Enlever (supprimer) des lignes par filtrage
  • Supprimer des colonnes
  • Développer des colonnes

Procédure


Afin de réaliser cette transformation, nous allons passer par les étapes suivantes
  • Etape 1, Importation des données dans Power Query
  • Etape 2, Création de quatre colonnes
    • Une première colonne d'index intitulé "Index P" qui affichera un numéro séquentiel pour chaque ligne et qui débutera à partir de 2
    • Une seconde colonne d'index intitulé "Index Q" qui affichera un numéro séquentiel pour chaque ligne et qui débutera à partir de 1
    • Une troisième colonne d'index intitulé "Index V" qui affichera un numéro séquentiel pour chaque ligne et qui débutera à partir de 0
    • Une dernière colonne intitulé "modulo" qui affichera le reste de la division entière par 3 car nous avons 3 données par produit.
  • Etape 3, Création de trois requêtes intermédiaires
    • La première "Produits" permettra de retrouver les produits
    • La seconde "Quantités" permettra de retrouver les quantités
    • Et la troisième "Prix de ventes" permettra de retrouver les prix de ventes
    • Cela sera réalisé en exploitant les données de la colonne "Modulo" qui permettra de définir si la donnée représente le nom du produit (valeur = 0), la quantité (valeur = 1), ou le prix de vente (valeur =2).
  • Etape 4, Création de la requête "Produits et Quantités"
    • Cette première requête permettra de fusionner les produits et les quantités en faisant une jointure entre les requêtes "Produits" et "Quantités". Pour cela, on utilisera les deux colonnes "Index P" et "Index Q" afin mettre en relation les deux requêtes.
  • Etape 5, Création de la requête "Finale"
    • Cette dernière requête permettra de fusionner les produits, quantités et prix de ventes en faisant une jointure entre les requêtes " Produits et Quantités " et "Prix de ventes". Pour cela, on utilisera les deux colonnes "Index P" et "Index V" afin mettre en relation les deux requêtes.
  • Etape 6, Nettoyage de la requête finale
  • Etape 7, Chargement des données dans Excel.

La démonstration en vidéo


Bien sûr il existe au moins deux autres manières de faire et je reviendrais dessus dans les jours à venir.
N'hésitez pas à laisser vos commentaires.

Pour information :

La démonstration a été réalisé avec la version 1806 d'Excel (Abonnement Office 365).
Il est tout a fait possible de transposer ces étapes sous Power BI

dimanche 8 juillet 2018

Power Query, Les paramètres régionaux d'une requête

Ce matin, j'ai croisé dans la rue un de mes followers sur Twitter, un passionner d'Excel qui débute avec les outils Power (Power Query, Power Pivot, … ).

Après quelques échanges, celui-ci m'a informé qu'il suivait une formation en ligne (vidéos en anglais) et qu'il avait des difficultés à réaliser une démo d'importation et de transformation de données à partir d'un fichier texte avec Power Query. Son problème est le suivant :

  • Le fichier source contient une colonne avec des ventes en dollars US au format "$29.5"

  • Lorsqu'il essaye de convertir le contenu de la colonne en nombre décimale, il obtient une erreur alors que lors de la démo en ligne tout fonctionne correctement.

D'où provient cette erreur ?

Ceci est l'une des premières difficultés que j'ai eu a géré lorsque j'ai suivi la formation "Analyzing and Visualizing Data with Excel" et toute personne qui importera avec Excel (ou Power BI) des données qui sont dans une version de langue différente de celles de l'application, sera confronté au même problème.

En effet, si vous utilisez une version en français d'Excel, celui-ci reconnait la virgule "," comme séparateur de décimale alors que dans le fichier source c'est le point "." qui est utilisé comme séparateur pour la partie décimale car les données proviennent d'une source en anglais.

Comment faire pour résoudre le problème ?

Pour résoudre ce problème, il vous suffit d'indiquer à Power Query quelle option régionale il doit utiliser lors de l'importation (de la lecture) des données comme ceci :

  1. Fichier > Options et paramètres > Options de requête
  2. Classeur actif > Paramètres régionaux > Anglais (Etats-Unis)

Après le paramétrage, la conversion fonctionne comme un charme


Remarque 01 :
Le choix des paramètres régionaux permet aussi à Excel de reconnaître correctement les dates qui en anglais sont au format "mm/jj/aaaa" alors qu'en français elle sont au format "jj/mm/aaaa".

Remarque 02 :
Si vous travaillez le plus souvent avec des données qui proviennent de sources en anglais, il est possible d'indiquer à Excel d'utiliser systématiquement l'anglais au lieu de la langue par défaut d'Excel.
Pour cela, procéder comme suit :

  1. Fichier > Options et paramètres > Options de requête
  2. Global > Paramètres régionaux > étapes de la requête > Toujours en anglais


vendredi 6 juillet 2018

La remise de 75% sur certains examens MCP prolongé jusqu'au 31 décembre 2018



Bonjour chers amis MCT

Pour ceux d'entre vous qui ont raté l'annonce faite ce jeudi dans la newsletter du programme MCT, voici de bonnes nouvelles pour nous tous.
La remise de 75% sur 13 examens en rapport avec les technologies stratégiques de Microsoft est prolongé jusqu'au 31 décembre 2018.

Les produits et technologies concernés sont

Azure/Cloud Platform

Data Management and Analytics

Office 365

Windows 10

Windows Server 2016


Pour en savoir,plus RDV sur la page  de votre tableau de bord formation : https://www.microsoft.com/en-us/learning/dashboard.aspx#mcpTermsNConditions

Source : Newsletter du programme MCT du 05/07/2018