Code M dans les colonnes personnalisées Power Query | LuckyTemplates

Dans le blog d'aujourd'hui, je vais présenter la solution au , y compris un cadre simple et généralisable pour incorporer du code M dans une colonne personnalisée Power Query. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

La possibilité de créer des colonnes dans Power Query à l'aide du code M augmente votre capacité à résoudre des problèmes plus complexes.

Avant d'aborder la solution, examinons la nature du problème. J'ai donné un grand ensemble de données de Kaggle de 93 ans de données Oscar. Nous voulions sélectionner la meilleure actrice, les gagnantes, et en particulier les femmes qui avaient remporté plus d'un prix de la meilleure actrice, puis visualiser à l'aide de la chronologie Queryon l'année au cours de laquelle elles ont remporté leur deuxième prix.

J'avais un certain nombre d'objectifs lors de la mise en place de ce problème. Sur quoi voulons-nous que le problème se concentre et quels sont les principaux objectifs ?

Et dans celui-ci, il y en avait trois.

Le premier était d' apprendre ou de développer une certaine pratique avec la construction Group By et All Rows . C'est quelque chose sur lequel je me suis vraiment concentré car il résout un large éventail de problèmes assez simplement et élimine souvent le besoin de certains DAX très complexes.

Le deuxième objectif est particulièrement destiné aux personnes qui n'étaient pas habituées à travailler en M. Je voulais créer un problème qui développerait un certain confort dans l'écriture de code M de base . Je voulais forcer ce mode de réflexion sur ce que c'est que de développer du code M de base et le processus pour le faire.

Selon un chapitre du livre de Gil Raviv sur Power Query, environ 40 % des problèmes de Power Query peuvent être résolus simplement via l'interface utilisateur. Mais cela, avec l'ajout d'un code M de base et la possibilité de créer des colonnes personnalisées, n'est vraiment pas si difficile. Vous pouvez augmenter ce pourcentage de plus de 80 %. Vous pouvez doubler vos capacités de résolution de problèmes avec des compétences simples et fondamentales.

La troisième chose que je veux faire est de présenter le visuel personnalisé Queryon Timeline à ceux qui ne le connaissent pas. C'est un que j'ai vraiment appris à aimer. Je l'ai utilisé dans un certain nombre de cas différents et je pense qu'il fournit simplement une façon vraiment unique de visualiser les données.

Table des matières

• Approche de la solution au problème de la semaine #9
• Utilisation de Grouper par dans Power Query
• Incorporation du code M Table.AddIndexColumn
• Visualisation à l'aide de Queryon Timeline Custom Visual
• Conclusion

Approche de la solution au problème de la semaine #9

En réfléchissant à ce problème, j'ai voulu faire une expérience de pensée. Et si au lieu d'un fichier CSV, je vous donnais juste 93 bouts de papier (un avec le gagnant de la meilleure actrice de chaque année), à ​​quoi ressemblerait la solution ?

Votre solution ressemblerait probablement à ce que j'ai ci-dessous.

Je pense que tout le monde aurait une solution relativement similaire à cela. Une fois que vous avez cette solution conceptuelle, il ne vous reste plus qu'à déterminer comment faire en sorte que Power Query exécute chacune de ces étapes.

Et donc, je vais juste prendre cela comme un aperçu général, puis expliquer comment ils opérationnalisent cette solution conceptuelle en quelque chose dans Power Query. L'approche que nous allons utiliser est ce que Melissa, notre gourou résident de Power Query, appelle apprendre par Google.

Maintenant, si nous revenons à notre modèle conceptuel, les deux premières étapes consistent à trier les feuillets en groupes par actrice et à éliminer tout groupe avec un seul feuillet dessus. Donc, nous voulons faire un décompte. Si nous ne savons pas comment faire cela, nous pouvons aller sur Google et essayer de rechercher un terme. Dans cet exemple, j'ai essayé "group in power query". Avec cela, nous pouvons trouver tout ce que nous devons savoir sur la façon de faire ce regroupement.

Utilisation de Grouper par dans Power Query

Ensuite, nous pouvons sauter dans Power Query et commencer à opérationnaliser notre solution. Ce que j'ai fait, c'est que dans ce tableau des Oscars, je viens de parcourir et d'extraire le fichier CSV et de filtrer les gagnantes de la meilleure actrice pour chaque année. Donc, nous n'avons que les 93 lignes du nom et de l'année du film.

Si nous allons dans Power Query et allons dans cette table, la première chose que nous voulons faire est ce Group By .

J'ai toujours aimé aller à Advanced simplement parce qu'il fournit juste une interface légèrement meilleure même si ce que nous faisons est assez simple.

Nous voulons regrouper par Name , puis prenons une colonne, AllData , et ce sera simplement notre All Rows. Au lieu de renvoyer uniquement l'agrégat, il renverra l'agrégation, mais toutes les lignes que nous voulons rester intactes.

Nous aurons une autre colonne appelée Count et nous la voulons quand nous allons Count Rows . Nous voulons trouver le nombre de feuillets dans chaque pile. Ensuite, nous cliquons sur OK.

On peut alors cliquer sur cet espace blanc dans le tableau, et on obtient un seul bordereau.

Mais pour ceux qui ont plusieurs comptes, vous verrez que nous obtenons tous les enregistrements.

Ensuite, nous voulons filtrer ceux (1). Donc, nous sommes en train de détruire toutes ces piles qui n'ont qu'un seul nom. Sélectionnez 1 et cliquez sur OK.

Et nous en sommes maintenant aux 14 lauréats multiples. Et nous en sommes maintenant aux 14 lauréats multiples. Si nous prenons et développons cela, nous pouvons retirer Name parce que nous avons déjà Name ici et garder juste year_film .

Nous en sommes maintenant à 30 rangées car certaines d'entre elles auront plusieurs victoires. Et nous pouvons même nous débarrasser de la colonne Count car nous avons déjà supprimé les 1 avec un seul.

Passons maintenant à la phase suivante de notre modèle conceptuel, qui consiste à trier les bulletins de chaque groupe par date ascendante, puis à choisir le deuxième jour dans chaque groupe.

Le tri est facile. Nous pouvons revenir dans Power Query et effectuer un tri croissant par actrice, puis par date.

Et il vous montrera un un et un deux.

Nous sommes donc bien triés, et maintenant nous voulons avoir comme un index ou un classement qui a pour chacun, et ensuite pouvoir prendre le deuxième dans chaque pile.

Incorporation du code M Table.AddIndexColumn

Et maintenant, nous revenons à notre approche "Appris par Google" où nous pouvons simplement rechercher un classement ou un index, mais nous pouvons également consulter le Guide de référence Microsoft . J'ai créé un outil externe que j'utilise tout le temps et qui vous fait simplement rebondir de LuckyTemplates vers le Guide de référence Microsoft.

Dans le Guide de référence Microsoft, tout est regroupé par fonction et comporte une zone de filtre dans laquelle vous pouvez rechercher tout ce dont vous avez besoin. Dans ce cas, nous tapons Rang ou Index dans la zone de filtre. Cela ne nous donne rien pour Rank, mais pour Index, nous avons ce Table.AddIndexColumn , qui est pertinent et applicable à ce que nous recherchons. Il fournit également quelques exemples d'utilisation de cette fonction.

Passons donc dans Power Query et regroupons notre table par Actrice.

Avec cela, nous obtenons le groupe de 14 gagnants par table. Et si nous allons dans l'un de ces tableaux, disons, Frances McDormand, nous pouvons voir que le tableau est trié par date.

Et puis, voici la grande étape, qui consiste à ajouter une colonne personnalisée Power Query et à entrer notre code M. Nous aurons le Table.AddIndexColumn , puis ajouterons le champ AllData . Nous appellerons notre nouvelle colonne (sous forme de texte) ici comme Index , et nous commencerons notre Index à un (1) et l'incrémenterons d'un (1).

Et nous obtenons cet indice parfait ici.

Maintenant, nous pouvons supprimer notre colonne AllData ici puisque tout est récupéré dans Custom . Ensuite, nous pouvons simplement développer la colonne Custom, supprimer Name, car nous avons déjà Name ici également. Avec cela, nous avons cet index numéroté pour chacun.

Enfin, nous pouvons faire ce que nous avons fait sur Count, c'est-à-dire filtrer tous ceux qui sont dans la deuxième victoire.

Et voilà. Voilà notre solution.

Visualisation à l'aide de Queryon Timeline Custom Visual

Il y a quelques autres choses que nous voulons faire ici, qui sont toutes basées sur l'implémentation de Queryon Visual . Nous allons faire une fusion et ajouter simplement l'URL de l'image.

Nous allons donc fusionner les requêtes, extraire cette table Actrices et nous fusionnerons sur Name . Nous obtenons 14 à 14 lignes correspondantes, ce qui est bien.

Et puis dans la colonne Actrices, nous allons simplement développer et nous développerons l'URL.

Et maintenant, nous avons cette colonne avec les URL.

Il y a une autre chose qui vient avec la connaissance de la chronologie de Queryon. Queryon fonctionne mieux lorsqu'il s'agit de dates complètes plutôt que d'années . Et donc, c'est quelque chose qui vient juste avec l'expérimentation, mais nous pouvons prendre la colonne Année et créer une colonne à partir d'exemples . Ce que nous voulons faire ici, c'est simplement transformer les années en dates.

Cela améliorera le formatage. C'est quelque chose que je ne m'attendrais pas à ce que vous sachiez dès le départ, mais cela vient avec la pratique avec Queryon. Nous cliquons sur OK, et cela entre comme valeur de date. Nous pouvons supprimer la table d'index car nous n'en avons plus besoin, et nous pouvons alors changer le nom de la colonne year_film en Year .

Passons maintenant à l'aspect visualisation de cela et je tiens à souligner l'entrée de l'un des membres de LuckyTemplates, Jose, qui a vraiment fait un excellent travail de visualisation.

Pour en revenir à Power Query pour la visualisation, il convient de mentionner que Microsoft a récemment mis en œuvre certaines modifications dans la manière dont il certifie les visuels personnalisés . Dans le cadre de cette certification, ils ont resserré l'utilisation d'images d'URL externes.

Si vous utilisez réellement la version de Queryon Timeline qui se trouve sur App Source (version 1.05), vous ne pourrez pas extraire ces images d'URL. Et donc, vous avez maintenant la possibilité d'utiliser cette 1.04, en gardant les images URL, où ils ont sur leur site un moyen d'intégrer des images binaires 64 directement dans le fichier PBIX.

Maintenant, j'ai mentionné le changement de cette année dans un champ de date, nous devons donc le connecter à notre modèle de données.

Changeons-le simplement de un à un à un à plusieurs , et Direction unique sur le filtre Croix, puis rendons cette relation active .

Ensuite, pour le champ URL de l'image, nous devons nous assurer que dans la catégorie Données, il ne s'affiche pas comme Non classé, mais comme URL de l'image .

Maintenant que tout est bien préparé, il nous suffit de faire glisser et de déposer les champs Name , Date et URL . Le reste devient un nettoyage très simple dans les options de format.

Power Query Table: How To Dynamically Merge Columns
How To Merge Queries In LuckyTemplates
Extract Values From Records And Lists In Power Query

Conclusion

Hopefully, I got you excited about adding M code to your repertoire. I’ve shown you how to set up a conceptual model and implement the Learn by Googling approach. I’ve walked you through the steps in incorporating M code into a custom column and visualizing the results using Queryon Timeline.

I hope you found that useful. Watch the full video below for more details on the tutorial and check out the links below for more related content.

All the best!