[PYTHON] Collection de techniques de requête de puissance, partie 1

J'ai résumé les cas d'utilisation de Power Query que j'utilise souvent. Je ne l'ai pas encore appris, il y a donc certains points que je ne peux pas comprendre, mais si vous remarquez des points, veuillez les signaler.

Caractéristiques de M

Pour le moment, je résumerai brièvement les caractéristiques de M.

• Sensible aux majuscules et minuscules
• Traitement strict des types de données
• L'index commence à 0
• Toutes les expressions sont évaluées en même temps

Le dernier point est un peu déroutant, mais le fait est qu'il n'est pas exécuté dans l'ordre à partir du haut, mais qu'il est traité selon la dépendance comme un logiciel de calcul de table.

Type de données

C'est aussi à peu près. Pour plus d'informations, il est préférable de consulter la référence MS.

liste

Une liste est un ensemble de valeurs avec un index basé sur 0. Pour faire une liste, arrangez simplement chaque élément avec `` {} '' comme indiqué ci-dessous. Il peut être plus rapide d'écrire directement une liste prédéterminée.

{1, 2, true, "abc", 3.1}

La liste étant un ensemble de valeurs ordonnées, vous pouvez la récupérer avec un index basé sur 0 à la fin.

{1, 2, true, "abc", 3.1}{4} #3.1 retours

Si vous souhaitez en récupérer plusieurs, utilisez List.Range. `` {0..10} '' est une liste de 11 de 0 à 10. Cela récupérera l'index 0 à un avant 5.

List.Range({0..10}, 0, 5)

résultat Notez que l'index affiché dans le résultat commence à 1, il est donc différent de l'index de la fonction. Par exemple, List.Range ({0..10}, 0, 5) {1} '' renvoie le deuxième élément 1 ''.

table

Les tableaux peuvent être créés avec la fonction `` #table () ''. Pour d'autres exemples, vous devriez consulter le site de Microsoft.

#table(
    {"Name", "Sex"},
    {
        {"Mike","M"},
        {"Anne","F"}
    }
)

une fonction

La définition de la fonction peut être écrite comme suit.

(x as number) as number =>
    let
        Ret = if x < 2 
            then x
            else Fib(x - 1) + Fib(x - 2)
    in
        Ret

C'est une fonction courante pour calculer le nombre de Fibonacci, mais comme vous pouvez le voir, la récurrence est également possible. Si vous enregistrez cette requête en tant que Fib, le Fib de la requête appellera lui-même cette fonction.

Voici une fonction qui convertit une chaîne de caractères à 8 chiffres telle que `` 20200401 '' en un type de date, et si elle ne peut pas être convertie avec succès, elle renvoie la chaîne de caractères telle quelle.

(x as text) =>
    let
        usrYear = Number.From(Text.Middle(x, 0, 4)),
        usrMonth = Number.From(Text.Middle(x, 4, 2)),
        usrDay = Text.Middle(x, 6, 2),
        isDD = Value.Is(Value.FromText(usrDay), type number),
        usrDate = if isDD 
            then Date.From(#date(usrYear, usrMonth, Number.From(usrDay)))
            else x
    in
        usrDate

Si vous fermez l'éditeur et sélectionnez cette requête, vous pouvez également saisir vous-même les paramètres et voir les résultats. Cette fonction peut être utilisée dans d'autres requêtes, il est donc pratique de créer des fonctions fréquemment utilisées dans les fonctions utilisateur de requête.

Utiliser des requêtes stockées en externe

C'est la méthode que j'ai trouvée par google, mais il est également possible de sauvegarder ces codes dans un fichier texte et de l'appeler. Si vous écrivez la partie requête avec un éditeur de texte approprié et que vous l'enregistrez, vous pouvez l'exécuter de la même manière avec la requête suivante. Ceci est utile si vous souhaitez utiliser des fonctions communes dans différents fichiers.

let
    Source = Text.FromBinary(File.Contents("C:\temp\func.m")),
    EvaluatedExpression = Expression.Evaluate(Source, #shared)
in
    EvaluatedExpression

Cela ne fonctionnera pas sans le paramètre partagé \ #. Le mystère est. Les fonctions récursives ne peuvent pas être exécutées correctement telles quelles, donc une certaine ingéniosité est requise.

Fib.m

let 
    Fib = (x as number) as number =>
        let
            Ret = if x < 2 
                then x
                else @Fib(x - 1) + @Fib(x - 2)
        in
            Ret
in
    Fib

Si vous imbriquez et intégrez la définition de fonction Fib elle-même de cette manière, vous pouvez l'utiliser même si vous l'importez sous forme de fichier. Il peut être préférable de l'écrire comme ceci car vous pouvez le copier sans être affecté par le nom de la requête. Il semble qu'il existe une règle pour ajouter `` @ '' au début lors d'un appel récursif, mais cela a fonctionné même s'il n'a pas été ajouté.

Combinez plusieurs fonctions dans un seul fichier

Organise plusieurs fonctions utilisateur séparées par des virgules et renvoie chacune sous la forme d'un enregistrement. SumProduct est une fonction que j'ai trouvée par google.

myfuncs.m

let 
    Fib = (x as number) as number =>
        if x < 2 then x else @Fib(x - 1) + @Fib(x - 2),
    Multiply = (x, y) => (x * y),
    SumProduct = (L1 as list, L2 as list) as number =>
        let
            Result = List.Accumulate(List.Positions(L1),
            0,
            (state, current) => state + L1{current} * L2{current})
        in
            Result,
    Ret = [
        Fib = Fib,
        Multiply = Multiply,
        SumProduct = SumProduct
    ]
in 
    Ret

Cela renvoie un enregistrement avec chaque nom de fonction comme clé et une fonction comme valeur, donc si vous lisez ce fichier texte avec une requête telle que myfuncs,

myfuncs[SumProduct]({1..10},{2..11})

Vous pouvez l'appeler avec `` () '' comme argument.

Utilisez le DataFrame de Python comme table.

Si vous le définissez pour utiliser Python, vous pouvez charger Pandas DataFrame. Si vous sélectionnez Python comme source de requête, l'éditeur s'ouvrira, donc j'écrirai le code ici, mais ici je vais créer le code avec un éditeur de texte approprié ou IDE et l'enregistrer dans un fichier séparé. Par exemple

bostondf.py

import pandas as pd
from sklearn.datasets import load_boston
boston_dataset = load_boston()
boston = pd.DataFrame(boston_dataset.data, columns=boston_dataset.feature_names)

Créez un fichier appelé et écrivez ce qui suit dans l'éditeur de requêtes.

let
    Source = Python.Execute(Text.FromBinary(File.Contents("c:\temp\bostondf.py"))),
    Ret = Source{[Name="boston"]}[Value]
in
    Ret

Ensuite, le bloc de données devient une table. Si vous souhaitez définir plusieurs trames de données dans un code, énumérez simplement les DF.

bostondf.py

import pandas as pd
from sklearn.datasets import load_boston
boston_dataset = load_boston()
boston = pd.DataFrame(boston_dataset.data, columns=boston_dataset.feature_names)
boston_01 = boston[:100]
boston_02 = boston[100:200]
boston_03 = boston[200:300]
boston_04 = boston[300:400]
boston_05 = boston[400:500]
boston_06 = boston[500:600]

Si vous écrivez la requête comme suit, une table composée de chaque DF et table sera renvoyée.

LoadPythonDFs.m

Python.Execute(Text.FromBinary(File.Contents("c:\temp\bostondf.py")))

Si vous enregistrez la requête qui a chargé ce code avec un nom approprié tel que LoadPythonDFs et créez une nouvelle requête comme suit, vous pouvez créer une fonction qui prend le nom DF de Python comme argument et prend DF.

(tName) => LoadPythonDFs{[Name=tName]}[Value]

L'appel de python prend beaucoup de temps, mais une fois que vous l'avez rempli, Power BI fonctionne légèrement (probablement). C'est un peu ennuyeux de se voir demander la permission chaque fois que j'édite du code python.

Lorsque le champ de nom de fichier n'est pas créé

Lorsque vous spécifiez un dossier comme source de données et importez plusieurs fichiers à la fois, le champ Source.Name peut ne pas être créé et vous ne savez peut-être pas de quel fichier se trouvent les données. Parfois, cela fonctionne, mais pour une raison quelconque, je fais souvent des erreurs dans mon environnement. Je pense que c'est un bug. Dans un tel cas, regardez la requête et trouvez la partie qui ressemble à ceci. Ajoutez une ligne entre les deux et modifiez les lignes suivantes comme suit: Cela ajoutera un élément Source.Name de félicitations.

Supprimer les champs à la fois

Lors de la suppression de champs inutiles, il est souvent décidé de ce qu'il faut supprimer. Je vais solidifier les champs qui ne font pas un tel tableau.

let
    UnecessaryFields = #table(
    {"DataName", "ListofFields"},
    {
        {"Report1",
            {"Champ inutile 1", "Champ inutile 2", "Champ inutile 3"}
        },
        {"Report2",
            {"Champ inutile 1", "Champ inutile 2", "Champ inutile 3"}
        }
    }
)
in
    UnecessaryFields

Et si vous l'écrivez comme ceci dans la requête, vous pouvez supprimer les champs spécifiés à la fois.

Remove_Fields = Table.RemoveColumns(
    "Tableau précédent", Table.SelectRows(UnecessaryFields, each [DataName] = "Report1"
        ){0}[ListofFields])

nombre aléatoire

Vous pouvez obtenir des nombres aléatoires de a à b avec `` Number.RandomB Between (a, b) '', mais si vous les ajoutez à la colonne tels quels, ils peuvent tous être le même nombre. (Parfois cela n'arrive pas. Mystère) Dans ce cas, si vous ajoutez ou soustrayez une colonne de nombres au premier argument, un nombre aléatoire différent sera calculé pour chaque enregistrement.

let
    ListN = #table({"i"}, List.Transform({1..10}, each {_})),
    #"Added Custom" = Table.Buffer(Table.AddColumn(ListN, "rnd", 
        each Number.RandomBetween(0 + [i] - [i], 100)))
in
    #"Added Custom"

two-way Lookup Par exemple, il existe deux tableaux: Enregistrez temporairement la première table en tant que personnes. Pour importer les éléments de la deuxième table de la première table (par exemple, vous voulez connaître le nom avec l'ID = 001), ajoutez une colonne personnalisée à la deuxième table et écrivez: Je vais. De cette façon, vous pouvez faire la même chose que coller une relation. Cependant, s'il y a plusieurs clés dans la table principale, une erreur se produit, il est donc nécessaire de définir à l'avance l'ID principal sur unique. La même chose peut être obtenue plus facilement en fusionnant les requêtes. Vous pouvez l'utiliser pour associer un code de couleur HTML à une valeur spécifique dans les données et modifier la couleur d'arrière-plan, etc. en fonction de la valeur dans le format conditionnel du rapport. Vous pouvez le faire sans l'utiliser.

Lien Collection

Enfin, les liens auxquels j'ai fait référence. Référence du langage de formule M officiel de Microsoft Power Query M Primer Introduction M or DAX Basics of M