PQ Abfrage mit verschiedenen Werten ausführen + Protokoll

13.10.2025 15:34:56

Christian

Hallo,

ich möchte euch fragen, ob ihr für meine Fragestellung in dieser Datei eine Lösung seht:

https://www.herber.de/bbs/user/179153.xlsx

Diese Datei entspricht leider aufgrund der Größenbeschränkung nicht dem vollen Umfang der Originaldatei.
Z.B. die Quelldatei für die PQ Abfrage fehlt, da diese 22 MB hat.

Aber es wäre trotzdem nett, wenn sich jemand mal den Code der Abfrage zHV anschaut.
Dort habe ich jetzt zum Zeigen einfach mal X und Y als Wert für die Referenzkoordinaten eingetragen.

In Tabelle2 findet ihr eine Liste von in der Bsp. Datei 3, in der Originaldatei 100 Koordinaten.

Mein Ziel ist es, herauszufinden, mit welcher der 100 Koordinaten die PQ Abfrage die meisten Ergebnisse (Anzahl der Zeilen) liefert.

Seht ihr da eine Möglichkeit?

Danke
Christian

AW: PQ Abfrage mit verschiedenen Werten ausführen + Protokoll

13.10.2025 23:26:40

Yal

Hallo Christian,

im Prinzip relativ einfach: isoliere die x und y aus der Abfrage 1 (Abfrage-Verweis und Spalten behalten), mache eine Gruppierung nach x und y, dabei die Anzahl entstehen lassen, nach dieser Anzahl absteigend sortieren, die erste 100 Treffer behalten und das Ergebnis ggü Abfrage 1 joinen, um die gleichen x|y zu behalten.

Bei den Latitude/Longitude hast Du aber das Problem, dass jede x|y eindeutig ist, spricht alle Anzahl nach Gruppierung sind 1. Du musst zuerst eine Rundung machen. Erste taugliche Ergebnisse mit einer Rundung nach 2 Nachkommastellen.

Zweitens, Du möchtest nicht die 100 ersten gerundeten Koordinate, sondern die Koordinate, die zusammen 100 Treffer haben. D.h. aus der Anzahl musst Du eine kumulierte Anzahl erzeugen, um diesen "unter 100" zu filtern. Kumul ist in Power Query entweder knifflig oder per Brute Force mit langer Rechenzeit zu haben. Man hilft sich in dem die sortierte Liste auf die ersten 100 Datensätze beschränkt wird, dann kumuliere.

Also:
mit

    Lat_gerundet = Table.AddColumn(#"Geänderter Typ", "Lat_gerundet", each Number.Round([Latitude], 2)),

    Long_gerundet = Table.AddColumn(Lat_gerundet, "Long_gerundet", each Number.Round([Longitude], 2))

erzeugst Du in der Hauptabfrage 2 neuen Spalten mit gerundeten Latitude/Longitude.

mit

let

    Quelle = Table.SelectColumns(zHV,{"Lat_gerundet", "Long_gerundet"}),

    #"Gruppierte Zeilen" = Table.Group(Quelle, {"Lat_gerundet", "Long_gerundet"}, {{"Anzahl", each Table.RowCount(_), Int64.Type}}),

    #"Sortierte Zeilen" = Table.Sort(#"Gruppierte Zeilen",{{"Anzahl", Order.Descending}}),

    #"Beibehaltene erste Zeilen" = Table.FirstN(#"Sortierte Zeilen",100),

    spIndex = Table.AddIndexColumn(#"Beibehaltene erste Zeilen", "Index", 1, 1, Int64.Type),

    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(spIndex, "Kumul", each List.Sum(List.Range(Table.Column(spIndex, "Anzahl"), 0, [Index]))),

    #"Gefilterte Zeilen" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte", each [Kumul] = 100)

in

    #"Gefilterte Zeilen"

holst Du dir die Top 100 Longitute/Latitude in einer separaten Abfrage, die Du dann mit der ersten in einer separaten Abfragen joinen muss, um die Ergebnisse zu isolieren.

VG
Yal

AW: PQ Abfrage mit verschiedenen Werten ausführen + Protokoll

13.10.2025 23:38:36

Christian

Hallo Yal,

es tut mir leid, aber ich habe beim Lesen immer mehr das Gefühl gehabt, du hast meinen Wunsch nicht verstanden.

Die vollständige CSV Datei enthält alle Haltestellen und Bahnhöfe in Deutschland mit Koordinaten und Daten wann sie zuletzt bedient wurden/werden.
Ich habe jetzt in Tabelle2 3 Koordinaten genannt, in dem Beispiel in Berlin, Hamburg und München.
Wenn ich den Code ausführe, werden mir alle Haltestellen angezeigt, die im Umkreis von 80 km Luftlinie der vorgegebenen Koordinate liegen und noch bedient werden, als bsp. bei mir zu hause ca. 24800.

Ich hatte eigentlich nur einen Automatismus gesucht, der die Abfrage je einmal mit jeder der Koordinaten in Tabelle2 ausführt und irgendwo festhält, wieviele Zeilen es jeweils mit den Koordinaten waren. Daraus hätte ich dann ablesen können, bei welcher es die meisten waren.

Ok, jetzt versuche ich doch nochmal hinter deine Nachricht zu steigen, in der Hoffnung dass wir vielleicht doch nicht aneinander vorbeireden.

Danke schonmal
Christian

2. Versuch deinen Beitrag zu verstehen

14.10.2025 00:01:18

Christian

Hallo Yal,
danke dir für die ausführliche Erklärung 👍

Mein Ziel ist allerdings ein bisschen anders:
Ich habe eine eigene Liste mit 100 Referenzkoordinaten (in Tabelle2) und möchte herausfinden, bei welcher dieser Koordinaten die Power-Query-Abfrage die meisten Treffer im 80-km-Umkreis liefert.

Dein Vorschlag mit der Gruppierung zählt ja, wie oft eine (oder gerundete) Koordinate in der zHV-Tabelle vorkommt. Das ist zwar auch spannend, trifft aber nicht ganz das, was ich suche.

Trotzdem danke für deine Idee – das hilft mir, den Ansatz besser einzuordnen!

Viele Grüße
Christian

AW: 2. Versuch deinen Beitrag zu verstehen

14.10.2025 00:11:45

Yal

Ja, tatsächlich nicht verstanden :-)
Challenge angenommen: für die gegebenen Koordinate alle Bahnhöfe/Haltestelle der erste Liste ermitteln, die in einem Umkreis von 80 km liegen.

AW: 2. Versuch deinen Beitrag zu verstehen

14.10.2025 01:31:03

Yal

Hallo Christian,

ich habe deine Liste im Blatt "Tabelle2" in einer Tabelle "tblEingang" umgewandelt, um die Daten einfacher in Power Query zu bringen.

Die Berechnung der Distanz habe ich in einer Funktion "Distanz" ausgegliedert:

let 

    Distanz = (lat1  as number, long1 as number, lat2 as number, long2 as number) =>

    let

        lat1 = lat1 * Number.PI / 180,

        long1 = long1 * Number.PI / 180,

        lat2 = lat2 * Number.PI / 180,

        long2 = long2 * Number.PI / 180,

        a = Number.Power(Number.Sin((lat2 - lat1) / 2), 2) 

            + Number.Cos(lat1) 

            * Number.Cos(lat2) 

            * Number.Power(Number.Sin((long2 - long1) / 2), 2),

        c = 2 * Number.Atan2(Number.Sqrt(a), Number.Sqrt(1 - a)),

        r = 6371.0088,

        dist = r * c

    in

        Number.Round(dist, 7)

in Distanz

Es nimmt die 2 Punkten in lat1|long1 und lat2|long2 an und liefert die Entfernung in Zehntelmillimeter ab (7 Nachkomma-Stellen :-)

Eine zweite Funktion "Zählen" rechnet pro Datensatz von "tblEingang" die Entfernung zu jeden einzelne Datensatz aus "zHV", filtert anhand der gegebene Max-Entfernung und liefert die Anzahl Treffer:

let

    //lat = 51, long = 6, MaxEntfernung = 80, //für Test

    Anzahl = (lat as number, long as number, MaxEntfernung as number) =>

    let

        Quelle = zHV,

        Entfernung_rechnen = Table.AddColumn(Quelle, "Entfernung", each Distanz([Latitude], [Longitude], lat, long)),

        #"Gefilterte Zeilen" = Table.SelectRows(Entfernung_rechnen, each [Entfernung] = MaxEntfernung),

        AnzahlZeile = Table.RowCount(#"Gefilterte Zeilen")

    in 

        AnzahlZeile

in

    Anzahl

Diese Funktion "Zählen" wird in der Abfrage "tblEingang" (weil die Quelle so heisst) angewendet:

let

    Quelle = Excel.CurrentWorkbook(){[Name="tblEingang"]}[Content],

    #"Geänderter Typ mit Gebietsschema" = Table.TransformColumnTypes(Quelle, {{"Lat", type number},{"Long", type number}}, "en-150"),

    #"Aufgerufene benutzerdefinierte Funktion" = Table.AddColumn(#"Geänderter Typ mit Gebietsschema", "Anzahl Treffer", each Zählen([Lat], [Long], 400))

in

    #"Aufgerufene benutzerdefinierte Funktion"

Ergebnisse kommen erst zustand, wenn eine große Entfernung (400 km) eingegeben wird.

Die Auslagerung der Zählung in einer separaten Funktion ist notwendig, da die Startpunkt jeweils anders sind und jedesmal neuberechnet werden.
Weitere Filterung kann man in der Funktion "Zählen" vornehmen, idealerweise vor der Berechnung der Distanz, weil diese Rechenzeitaufwendig ist (wobei PQ sicher eine eigene Abkürzung vornimmt).

Ich hoffe, Du kommst zurecht mit diesen Angaben.

VG
Yal

AW: 2. Versuch deinen Beitrag zu verstehen

14.10.2025 12:29:54

Christian

Hallo Yal,
danke dir, das sieht super aus! 👍

Jetzt habe ich verstanden, wie du das über die beiden Funktionen aufziehst – das ist tatsächlich eine saubere Lösung.
Die Idee, die Distanzberechnung und das Zählen zu trennen, gefällt mir gut, das macht den Code viel übersichtlicher.

Ich vermute, dass ich in meiner kleinen Testdatei bei 80 km einfach keine Treffer bekomme, daher erst bei 400 km etwas sehe. In der großen Originaldatei sollte das dann passen.

Danke dir für die Mühe, das so ausführlich zu erklären – damit komme ich sehr gut zurecht!

Viele Grüße
Christian

PS: folgende Codes nutze ich jetzt

let 

    Distanz = (lat1 as number, long1 as number, lat2 as number, long2 as number) =>

    let

        // Umrechnung in Radiant

        lat1r = lat1 * Number.PI / 180,

        long1r = long1 * Number.PI / 180,

        lat2r = lat2 * Number.PI / 180,

        long2r = long2 * Number.PI / 180,



        // Haversine-Formel

        a = Number.Power(Number.Sin((lat2r - lat1r) / 2), 2) 

            + Number.Cos(lat1r) * Number.Cos(lat2r) * Number.Power(Number.Sin((long2r - long1r) / 2), 2),

        c = 2 * Number.Atan2(Number.Sqrt(a), Number.Sqrt(1 - a)),

        r = 6371.0088, // Erdradius in km

        dist = r * c

    in

        Number.Round(dist, 7)

in

    Distanz

let

    Zählen = (lat as number, long as number, MaxEntfernung as number) =>

    let

        Quelle = zHV,

        

        // Distanz berechnen für jeden Punkt

        MitEntfernung = Table.AddColumn(

            Quelle,

            "Entfernung",

            each Distanz([Latitude], [Longitude], lat, long),

            type number

        ),



        // Filter auf Umkreis

        Gefiltert = Table.SelectRows(MitEntfernung, each [Entfernung] = MaxEntfernung),



        // Anzahl Treffer zählen

        AnzahlTreffer = Table.RowCount(Gefiltert)

    in 

        AnzahlTreffer

in

    Zählen

let

    Quelle = Excel.CurrentWorkbook(){[Name="tblEingang"]}[Content],



    // Umwandeln in Zahl (mit internationalem Dezimalpunkt)

    MitZahlentyp = Table.TransformColumnTypes(

        Quelle,

        {{"Lat", type number}, {"Long", type number}},

        "en-150"

    ),



    // MaxEntfernung kann hier angepasst werden

    MaxEntfernung = 80,



    // Treffer pro Zeile zählen

    MitTreffern = Table.AddColumn(

        MitZahlentyp,

        "Anzahl Treffer",

        each Zählen([Lat], [Long], MaxEntfernung),

        Int64.Type

    ),



    // Sortieren nach Trefferzahl absteigend

    Sortiert = Table.Sort(MitTreffern, {{"Anzahl Treffer", Order.Descending}})

in

    Sortiert

AW: 2. Versuch deinen Beitrag zu verstehen

14.10.2025 00:16:30

Christian

Hallo yal, von mir aus nehme ich auch einen vba Code der 100x die Abfrage ändert bei den Koordinaten, aktualisiert und dir Anzahl der Zeilen neben die koordinaten in tab2 schreibt. Hauptsache es funktioniert

PQ Abfrage mit verschiedenen Werten ausführen + Protokoll

Forumthread: PQ Abfrage mit verschiedenen Werten ausführen + Protokoll

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

PQ Abfrage mit verschiedenen Werten ausführen + Protokoll

Forumthread: PQ Abfrage mit verschiedenen Werten ausführen + Protokoll

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread