[PYTHON] Power Query Technique Collection Teil 1

Ich habe die Power Query-Anwendungsfälle zusammengefasst, die ich häufig verwende. Ich habe es noch nicht gelernt, daher gibt es einige Punkte, die ich nicht verstehen kann. Wenn Sie jedoch Punkte bemerken, weisen Sie bitte darauf hin.

Eigenschaften von M.

Vorerst werde ich die Merkmale von M kurz zusammenfassen.

• Groß- und Kleinschreibung beachten
• Strenger Umgang mit Datentypen
• Index beginnt bei 0
• Alle Ausdrücke werden gleichzeitig ausgewertet

Der letzte Punkt ist etwas verwirrend, aber der Punkt ist, dass er nicht in der Reihenfolge von oben ausgeführt wird, sondern entsprechend der Abhängigkeit wie eine Tabellenberechnungssoftware verarbeitet wird.

Datentyp

Das ist auch ungefähr so. Weitere Informationen finden Sie in der MS-Referenz.

aufführen

Eine Liste besteht aus einer Reihe von Werten mit einem 0-basierten Index. Um eine Liste zu erstellen, ordnen Sie jedes Element wie unten gezeigt mit {} an. Es kann schneller sein, eine vorgegebene Liste direkt zu schreiben.

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

Da es sich bei der Liste um eine Reihe geordneter Werte handelt, können Sie sie am Ende mit einem 0-basierten Index abrufen.

{1, 2, true, "abc", 3.1}{4} #3.1 kehrt zurück

Wenn Sie mehr als eine abrufen möchten, verwenden Sie List.Range. {0..10} ist eine Liste von 11 von 0 bis 10. Dadurch wird der Index 0 bis 1 vor 5 abgerufen.

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

Ergebnis Beachten Sie, dass der im Ergebnis angezeigte Index bei 1 beginnt und sich daher vom Index in der Funktion unterscheidet. Zum Beispiel gibt List.Range ({0..10}, 0, 5) {1} das zweite Element 1 zurück.

Tabelle

Tabellen können mit der Funktion #table () erstellt werden. Weitere Beispiele finden Sie auf der Microsoft-Website.

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

Funktion

Die Funktionsdefinition kann wie folgt geschrieben werden.

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

Dies ist eine übliche Funktion zur Berechnung der Fibonacci-Zahl. Wie Sie jedoch sehen können, ist auch eine Wiederholung möglich. Wenn Sie diese Abfrage als Fib speichern, ruft die Fib in der Abfrage diese Funktion selbst auf.

Das Folgende ist eine Funktion, die eine 8-stellige Zeichenfolge wie "20200401" in einen Datumstyp konvertiert. Wenn sie nicht erfolgreich konvertiert werden kann, gibt sie die Zeichenfolge so zurück, wie sie ist.

(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

Wenn Sie den Editor schließen und diese Abfrage auswählen, können Sie die Parameter auch selbst eingeben und die Ergebnisse anzeigen. Diese Funktion kann in anderen Abfragen verwendet werden, daher ist es praktisch, häufig verwendete Funktionen in den Abfragebenutzerfunktionen zu erstellen.

Verwenden Sie extern gespeicherte Abfragen

Dies ist die Methode, die ich von Google gefunden habe, aber es ist auch möglich, diese Codes in einer Textdatei zu speichern und aufzurufen. Wenn Sie den Abfrageteil mit einem geeigneten Texteditor schreiben und speichern, können Sie ihn mit der folgenden Abfrage auf die gleiche Weise ausführen. Dies ist nützlich, wenn Sie gemeinsame Funktionen in verschiedenen Dateien verwenden möchten.

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

Ohne den gemeinsam genutzten Parameter \ # funktioniert dies nicht. Geheimnis ist. Rekursive Funktionen können nicht so gut ausgeführt werden, wie sie sind, daher ist ein gewisser Einfallsreichtum erforderlich.

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

Wenn Sie die Fib-Funktionsdefinition selbst auf diese Weise verschachteln und einbetten, können Sie sie auch dann verwenden, wenn Sie sie als Datei importieren. Es ist möglicherweise besser, es so zu schreiben, da Sie es kopieren können, ohne vom Abfragenamen beeinflusst zu werden. Es scheint, dass es eine Regel gibt, um "@" am Anfang hinzuzufügen, wenn es rekursiv aufgerufen wird, aber es hat funktioniert, auch wenn es nicht hinzugefügt wurde.

Kombinieren Sie mehrere Funktionen in einer Datei

Ordnet mehrere durch Kommas getrennte Benutzerfunktionen an und gibt jede als Datensatz zurück. SumProduct ist eine Funktion, die ich von Google gefunden habe.

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

Dies gibt einen Datensatz mit jedem Funktionsnamen als Schlüssel und einer Funktion als Wert zurück. Wenn Sie also diese Textdatei mit einer Abfrage wie "myfuncs" lesen,

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

Sie können es mit () als Argument aufrufen.

Verwenden Sie Pythons DataFrame als Tabelle.

Wenn Sie Python verwenden, können Sie Pandas DataFrame laden. Wenn Sie Python als Abfragequelle auswählen, wird der Editor geöffnet, sodass ich den Code hier schreibe. Hier erstelle ich den Code mit einem geeigneten Texteditor oder einer geeigneten IDE und speichere ihn als separate Datei. Zum Beispiel

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)

Erstellen Sie eine Datei mit dem Namen und schreiben Sie Folgendes in den Abfrageeditor.

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

Dann wird der Datenrahmen zu einer Tabelle. Wenn Sie mehrere Datenrahmen in einem Code definieren möchten, listen Sie einfach die DFs auf.

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]

Wenn Sie die Abfrage wie folgt schreiben, wird eine Tabelle zurückgegeben, die aus jedem DF und jeder Tabelle besteht.

LoadPythonDFs.m

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

Wenn Sie die Abfrage, die diesen Code geladen hat, unter einem geeigneten Namen wie LoadPythonDFs speichern und eine neue Abfrage wie folgt erstellen, können Sie eine Funktion erstellen, die den DF-Namen von Python als Argument und DF verwendet.

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

Das Aufrufen von Python ist sehr zeitaufwändig, aber sobald Sie es ausgefüllt haben, funktioniert Power BI (wahrscheinlich) leicht. Es ist etwas ärgerlich, jedes Mal um Erlaubnis gebeten zu werden, wenn ich Python-Code bearbeite.

Wenn das Feld Dateiname nicht erstellt wird

Wenn Sie einen Ordner als Datenquelle angeben und mehrere Dateien gleichzeitig importieren, wird das Feld Source.Name möglicherweise nicht erstellt und Sie wissen möglicherweise nicht, um welche Datei es sich bei den Daten handelt. Manchmal funktioniert es, aber aus irgendeinem Grund mache ich oft Fehler in meiner Umgebung. Ich denke, das ist ein Fehler. Schauen Sie sich in einem solchen Fall die Abfrage an und suchen Sie das Teil, das so aussieht. Fügen Sie eine Zeile dazwischen hinzu und ändern Sie die folgenden Zeilen wie folgt: Dadurch wird ein Glückwunschelement für Source.Name hinzugefügt.

Felder sofort löschen

Beim Löschen unnötiger Felder wird häufig entschieden, was gelöscht werden soll. Ich werde die Felder verfestigen, die eine solche Tabelle nicht bilden.

let
    UnecessaryFields = #table(
    {"DataName", "ListofFields"},
    {
        {"Report1",
            {"Unnötiges Feld 1", "Unnötiges Feld 2", "Unnötiges Feld 3"}
        },
        {"Report2",
            {"Unnötiges Feld 1", "Unnötiges Feld 2", "Unnötiges Feld 3"}
        }
    }
)
in
    UnecessaryFields

Und wenn Sie es so in die Abfrage schreiben, können Sie die angegebenen Felder sofort löschen.

Remove_Fields = Table.RemoveColumns(
    "Vorherige Tabelle", Table.SelectRows(UnecessaryFields, each [DataName] = "Report1"
        ){0}[ListofFields])

Zufallszahl

Sie können Zufallszahlen von a nach b mit Number.RandomBetween (a, b) erhalten, aber wenn Sie sie der Spalte so hinzufügen, wie sie sind, können sie alle dieselbe Zahl sein. (Manchmal passiert es nicht. Rätsel) In diesem Fall wird, wenn Sie dem ersten Argument eine Zahlenspalte hinzufügen oder davon abziehen, für jeden Datensatz eine andere Zufallszahl berechnet.

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 Zum Beispiel gibt es zwei Tabellen: Speichern Sie die erste Tabelle vorübergehend als Personen. Fügen Sie der zweiten Tabelle eine benutzerdefinierte Spalte hinzu und schreiben Sie: Um die Elemente aus der zweiten Tabelle aus der ersten Tabelle zu entfernen (z. B. möchten Sie den Namen mit der ID = 001 kennen). Ich werde. Auf diese Weise können Sie dasselbe tun wie eine Beziehung einfügen. Wenn die Mastertabelle jedoch mehrere Schlüssel enthält, tritt ein Fehler auf. Daher muss die Master-ID im Voraus auf eindeutig gesetzt werden. Dasselbe kann einfacher durch Zusammenführen von Abfragen erreicht werden. Sie können dies verwenden, um einen HTML-Farbcode einem bestimmten Wert in den Daten zuzuordnen und die Hintergrundfarbe usw. abhängig vom Wert im bedingten Format des Berichts zu ändern. Sie können es tun, ohne es zu verwenden.

Link-Sammlung

Zum Schluss die Links, auf die ich mich bezog. Microsoft Official M Formula Language Reference Power Query M Primer Introduction M or DAX Basics of M