 |
 |
 |
PowerQuery
- Croiser des données
- Si le TCD permet, sous réserve d'une source adaptée, de croiser des axes d'analyse pour effectuer des statistiques, il ne permet pas de réaliser un tableau à double entrée purement textuel.
Dans cet exemple nous avons une colonne de codes et une colonne de nuances que nous souhaitons transformer en tableau à double entrée
La source doit impérativement être sous forme de tableau structuré (cf. Tableaux) et il est conseillé de lui donner un nom signifiant, ici TNuances.
- Depuis l'onglet PowerQuery (2010 & 2013) ou l'onglet Données (2016) on bascule le tableau dans PowerQuery au moyen du bouton A partir d'un tableau, ce qui ouvre l'interface spécifique de PowerQuery décrite ici.
- Le basculement du tableau TNuances a créé une requête de même nom avec 2 étapes :
- Source, qui établit le lien avec le tableau source
- Type modifié qui correspond au typage automatique des données chargées
- Dans la grande majorité des cas, le typage automatique est bon mais ici nous avons des codes numériques, saisis en texte commençant par des 0 non significatifs et nous changeons le type de la colonne Code en la sélectionnant et en utilisant le bouton Type de données de l'onglet Accueil (ou via un clic droit).
- Afin d'obtenir le tableau à double entrée nous allons réaliser les étapes suivantes :
- supprimer les éventuels doublons, un seul élément pouvant figurer au croisement de la référence et de la couleur.
Ceci se fait très simplement en sélectionnant les 2 colonnes et via le clic droit (ou l'onglet Accueil, Supprimer des lignes).
- Comme nous allons devoir utiliser 2 versions de cette requête, l'une pour déterminer le nombre de coloris par code, l'autre pour
aller jusqu'au résultat attendu, il nous faut :
. dupliquer la requête via un clic droit sur le nom de la requête,
. renommer le duplicata TNuances2
et effectuer des étapes différentes sur chacune des deux versions.
- Sur TNuances2
- nous trions maintenant, via la liste déroulante en tête de colonne, sur les colonnes Code et Nuance
- puis, afin de garder trace de ce tri, nous ajoutons une colonne Index depuis l'onglet Ajouter une colonne
- Sur TNuances
- nous effectuons, depuis l'onglet Transformer, un regroupement par code, avec comptage des lignes en gardant le titre
Nombre proposé.
Il nous reste donc les colonnes Code et Nombre.
- nous allons décomposer la colonne Nombre en autant de lignes qu'indiqué dans cette colonne via 2 étapes :
. ajout d'un colonne personnalisée depuis l'onglet Ajouter une colonne, colonne nommée Nuances dans laquelle nous insérons
une formule de calcul
=List.Numbers([Nombre], [Nombre], -1)
ce qui a pour résultat d'afficher List dans cette nouvelle colonne équipée près de son nom d'une double flèche
. clic sur cette flèche pour développer le contenu et arriver à l'objectif énoncé.
- Afin de préparer le tableau à double entrée nous allons utiliser les nombres obtenus pour créer le titre des futures colonnes de
nuances en ajoutant une nouvelle colonne nommée Titres avec la formule de concaténation suivante :
=Nuance"&Text.From([Nuances])
- nous trions ensuite sur les colonnes Code et Titres puis ajoutons une colonne Index.
- Nous allons maintenant rapatrier dans notre requête les nuances disparues lors du regroupement en croisant TNuances
avec TNuances2 : onglet Accueil, Fusionner les requêtes, en les mettant en relation sur leur colonne Index, à sélectionner
dans chaque requête, sur la base d'une jointure interne.
- Nous obtenons une colonne contenant Table dont nous développons le contenu avec la double flèche en ne sélectionnant que
Nuance
- Nous supprimons les 3 colonnes devenues inutiles pour conserver Code, Titres et Nuance.
- Il nous reste à accomplir la dernière étape : sélectionner la colonne Titres puis, depuis l'onglet Transformer,
Colonne de tableau croisé dynamique nous distribuons Nuance sans agréger les données,
c’est-à-dire sans effectuer de calcul comme dans un TCD classique.
- Nous avons terminé notre traitement et pouvons basculer le résultat obtenu dans Excel en utilisant le 1er bouton à gauche
de l'onglet Accueil, Fermer et Charger dans, choisir Table et nouvelle Feuille ou l'emplacement de notre choix.
- Excel gérant automatiquement l'ajout ou supression de lignes dans les tableaux structurés, la requête étant par essence
dynamique, il suffit d'utiliser le bouton Actualiser Tout de l'onglet Données pour que le tableau à double entrée reflète
les changement de la source.
A noter qu'il serait possible de ne pas dupliquer la requête et de la croiser avec une de ses propres étapes mais cela nécessite
de modifier le code M qui se crée au fur et à mesure de nos manipulations. Ce qui n'est possible qu'avec un peu de pratique...