Office: Power Query grottenlangsam

Helfe beim Thema Power Query grottenlangsam in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo, bei der Fragestellung "Text nur am Anfang suchen und ersetzten" ging es darum, eine Spalte zu trennen, die durch eine PQ-Abfrage geliefert... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von WS-53, 1. Januar 2019.

  1. Power Query grottenlangsam


    Hallo,

    bei der Fragestellung "Text nur am Anfang suchen und ersetzten" ging es darum, eine Spalte zu trennen, die durch eine PQ-Abfrage geliefert wurde. Mit der Abfrage von forebet.com liest EuroCafe Fußballbegegnungen aus, die er dann weiterverarbeitet.

    Dummerweise werden seit einiger Zeit die Spielpaarungen nicht mehr in getrennten Spalten geliefert. Ich habe dazu dann ein Makro entwickelt, das die Spielpaarungen in "Heim" und "Gast" aufteilt. Ohne, dass ich hierbei etwas bewusst optimiert hätte, werden aus der Kombination Makro & Formeln, auch 1.000 Spielpaarungen innerhalb einer Sekunde aufgeteilt.

    Da ich in diesem Zusammenhang wieder einmal mit Power Query in Berührung kam, vom dem ich bisher eigentlich keine Ahnung hatte, kam mir die Idee, die Logik des VBA-Makros und der Formeln, in einer Power Query Abfrage unterzubringen.

    Mit sehr viel googeln und testen und noch mehr googeln, habe ich es tatsächlich geschafft, eine PQ-Abfrage zu realisieren, die genau das macht, was auch das Makro macht. Insofern ist Power Query tatsächlich unheimlich leistungsfähig.

    Aber dass, was mit VBA für 1.000 Zeilen nur 1 Sekunde benötigt, braucht bereits für knapp 100 Zeilen mit der Power Query Abfrage schon 35 Sekunden. Bei kanpp 1.000 Zeilen, dauert es dann schon rund 5 Minuten.

    Und jedes Mal, wenn ich etwas an der Abfrage ändern will, dauert es immer ewig, bis diese editierbereit ist.

    Deshalb die Fragen: Lässt sich Power Query irgendwie beschleunigen und wie kann ich es erreichen, dass beim Öffnen einer Abfrage nicht immer alles geladen wird und es somit ewig dauert, bis ich an dieser etwas ändern kann?

    Anbei: Excelmappe mit Makro, Excelmappe ohne Makro und Code im Tabellenblatt "Makro Code", sowie die Excelmappe mit den PQ-Abfragen. Bei der 3. Abfrage handelt es sich dabei um die Zusammenführung von Abfrage 1 und Abfrage 2.

    Aber wenigstens hat mir dieses "Übungsprojekt" einige Erfahrungen im Umgang mit Power Query gebracht.

    :)
     
  2. Hallo,

    ich habe mir nun auch die weiterführenden Beschreibungen auf Chris Webbs`s Blog angesehen und mit dem Post von Brandon vom 16. Dezember 2015 113:02 eine Variante gefunden, bei der die Liste mit den Ersetzungen (Was soll durch was ersertzt werden), mit der Funktion "List.Buffer" gepuffert wird.

    Bei dieser Variante sollte das ganze mehr oder weniger genauso schnell, wie das VBA-Makro ablaufen. Das Problem somit gelöst sein.

    Hier der Code:

    PHP:
             let

    Replacements 
    = List.Buffer(Table.ToRows(Excel.CurrentWorkbook(){[Name="Replacements"]}[Content])),
    TextToChange Excel.CurrentWorkbook(){[Name="Text"]}[Content],
    ReplacementFunction each List.Accumulate(Replacements_, (tr) => Text.Replace(tr{0}, r{1})),
    Output Table.AddColumn(TextToChange"Changed_Text"each ReplacementFunction([Text]))

    in
    Output
     
  3. Ohne den Fall konkret angesehen zu haben: Ich halte wenig von solchen pauschalen und undifferenzierten Aussagen. Was Du ungebremst sagen darfst: Deine persönliche Umsetzung eines Falles mit Power Query ist grottenlangsam. Dass es auch anders geht, wird Dir ja schon spätestens im zweiten Beitrag bewusst.

    Vor einer Umsetzung sollte man erst einmal über Abläufe nachdenken. Power Query ist technisch nahe einer Datenbanklösung (SQL), und da sind Methoden der Massendatenverarbeitung hervorstechendes Merkmal. Massendatenverarbeitung (alles auf einmal) ist dann etwas anderes als das gewohnte Rennen in Schleifen, und das sollte man wiederfinden in Abläufen und konkreten Codes.
     
  4. Power Query grottenlangsam

    Hallo Eberhard,

    für mich hat es sich so dargestellt. Deshalb habe ich es auch so formuliert.

    Dazu habe ich auch die Fragestellung und meinen Lösungsweg beschrieben und gefragt, ob es evtl. einen besseren Lösungsansatz gibt.

    Was ist daran falsch?

    Parallel habe ich ja auch weiter geforscht und nun eine Lösung gefunden, die vermutlich das Problem löst.

    Leider bringe ich diese Lösung bei mir nicht erfolgreich zum laufen. Hilfreich wäre somit, wenn jemand, der schon mehr PQ-Erfahrung hat, den Fehler finden und beseitigen könnte.
     
  5. Deine Dateien sind eine Bedrohung für meine Installation, was schon damit beginnt, dass da irgendwelche Wiederherstellungen notwendig waren. Mit solchen Ausgangssituationen kenne ich mich nicht aus, und ich lege auch keinen Wert darauf, das zu ändern. Dann hat es beim Abbrechen meine Power Query-Aktivierung zerschossen. Nein, das hat Virencharakter.
    Der Link funktioniert auch nicht.

    Ich würde es in der Weise probieren, wie es Aloys in verlinkten Ausgangsthema mit Beitrag #2 einleitet: Einmalig die Mannschaften in einer Spalte in einer zusätzlichen Tabelle festhalten. Dann hat man Tabelle gegen Tabelle und kann hervorragend und einfach arbeiten. So wie nachfolgend sähe es in Access in Jet-SQL aus:
    Code:
    Dort bietet diese Gestaltung sogar die Möglichkeit zur Indexnutzung.
     
  6. Die Ursache bin ich, da WS-53 mir helfen wollte *wink.gif*

    Das Probelm besteht aber im Ursprung zwischen Query und VBA, unter bestimmten Konstellationen passiert es, das man 10mal eine Aktualisierung starten kann und beim 11mal zerschiesst Excel die Datei, macht dann eine Wiederherstellung und verliert die Verbindung zu dem VBA oder eben zu Query.

    MS hat sich zu dem Problem bis jetzt nicht gemeldet, das Problem besteht aber seit ca. 2013 mit der Umstellung von Add auf integriert in Excel!


    Selbst wenn man nur eine Abfrage mit mehr als 500 Datensätze startet, am Ende nur 50 rauskomnmen wegen den Selktionskriterien, sieht man im Taskmanager das viele GB und CP und verbaucht werden, obwohl man keine Aktualisierung gestartet hatte, es reicht unter Daten alleine Abfragen und Verbindungen zu aktualisieren und schon fängt die Schei.... an.

    Da es zum Beispiel keine Möglichkeit gibt mehrere Tabellen mit unterschiedlichen Namen zu laden, musste ich pr Liga eben eine Abfraqge starten. Am Ende wurden das dann über 80 für Gesamt und jeweils das gleiche für Heim und Auswärts. Also insgesamt mehr als 180 Abfragen in einer Datei *entsetzt

    Wie gesagt, Query versucht erst alle Daten zu laden um dann nach der Selektionsvariante nur die Ergebnisse zurück zu geben die auch gefilter bzw. definiert wurden, aber erst mal alle und ohne Aktualsierung!

    Hilfe könnte Access bieten, wenn man dort das gleiche anweden konnte wie in Excel *eek.gif* ist aber nicht so.

    Ich gehe also zur Steinzeit zurück und markiere alles und kopiere es dann jeden Tag nach Excel und das ist dann ein Fortschritt, weil Excel einem dann nicht die Dateien zerschiesst *wink.gif*

    Sorry WS-53 aber da kann dann nur der Guru helfen der mal einen Beitag über MS-Query geschrieben hatte und der mir auf meine Fragen auch einmal geantwort hatte *biggrin.gif* aber dann nie wieder *eek.gif* Das Makro habe ich noch immer und es würde auch laufen, nur das Makro von xlph läuft halt nicht mehr wegen den Änderungen auf der Seite.

    Mfg EC
     
    EuroCafe, 8. Januar 2019
    #6
  7. Was auch sonst, wenn es um externe Quellen geht, auf die man keinen Einfluss hat. Eine Website ist dann auch nicht eine klassische Tabelle, und eine dynamisch aufgebaute Tabelle (durch CMS erzeugt) in Verbindung mit Internet ist auch keine optimale Gestaltung.

    Daneben: Ich hatte seinerzeit Kurse von Investmentfonds von Websites importiert. Das ging damals auch nur eine Zeit lang, weil Anbieter das automatisierte Herunterladen durch Umgestaltungen immer mal wieder erschweren und unterbrechen.
    Auf der besseren Seite steht man, wenn der Anbieter einen Import ermöglicht, z.B. mit der Bereitstellung von entsprechenden CSV's.
     
  8. Power Query grottenlangsam

    Moin Moin zusammen,

    ich hab mich bisschen an dem Ansatz von WS53 #2 festgebissen.

    Ich denke auch zu Wissen wo der Fehler ist. Und zwar stolpert der darüber dass er mit der Text.Replace-Funktion Text braucht (in allen Ebenen - Originaltext - Suchwerte - Ersetzwerte)
    In dem Beispiel werden aber auch Zahlen ersetzt. Daher hakt er jedes mal mit dem Fehler aus.

    Wenn die Replace Liste indes mit einer reinen TextTabelle ersetzt wird, läuft er problemlos durch.

    Eine probate Lösung hab ich aber noch nicht... aber ich beisse weiter ;-D

    vielleicht hat ja jemand die entsprechende function aus dem MCode parat .. sowas wie

    Any.Replace *tongue.gif* *grins


    Grüße Jack


    Soo .. manchmal hilft es das ganze auch niederzuschreiben =)

    Bisschen Denken Suchen und Probieren bringt mich auf folgende Lösung
    (Ich hab dem jetzt einfach gesagt: Egal was, mach nen Text draus)

    Code:
    Ps:

    Replacer.ReplaceValue brachte indes kein Ergebnis, da er hierbei den gesamten String untersucht und nicht wie bei Replacer.ReplaceText alle Teile des String
    (Denk ich zumindest- da ich keine Doku dazu hab, das Verhalten aber dem entspricht)
     
    jack_D, 9. Januar 2019
    #8
  9. Hallo Jack,

    das ist schon mal ein sehr guter Hinweis. Ich habe nun einmal in der Replaements-Tabelle die Zahlenwerte 0-9 um einen Buchstaben ergänzt. Schon zeigt mir der letzte Schritt "Output" allen anderen Ersetzungen korrekt an.

    Allerdings wenn ich dann das Ergebnis mit "Schließen & laden" in das Tabellenblatt übernehmen wollte, dann wurden mir immer noch alle 86 Sätze als Fehlerhaft angezeigt. Derst nachdem ich das Ergebnis noch mit dem Type "Text" versehen hatte, konnte ich das Ergebnis auch in die Exceltabelle übernhemen.

    Ich habe auch die Parameter der Replace-Funktion um die Zusätze "type text" versehen, um die Zahlenwerte ersetzen zu können. Dies hat aber auch nicht geholfen.

    Aber als ich dann in "Replacements" die Zahlen als Text erfasst hatte, dann lief es problemlos durch.

    Zum Test habe ich dann meinen Input auf über 5.000 Zeilen erhöht. Die PQ-Abfrage ist dann aber auch nur rund 1 Sekunde gelaufen.

    Somit kann ich abschliessend sagen, das es mit dem workaround, die Zahlen als Text zu erfassen, problemlos läuft und dass die List-Buffer-Variante auch wahnsinnig schnell ist.

    Un
     
  10. Hallo Jack,

    da ich nun eine ganze weile mit Test und Antworten beschäftigt war, hatte ich deine Lösung erst nach dem Senden meines neunen Beitrags gesehen.

    Die Idee hatte ich auch. Aber meine Umsetzung:

    ReplacementFunction = each List.Accumulate(Replacements, _, (t, r) => Text.Replace(t, r{0}, r{1})),

    nach

    ReplacementFunction = each List.Accumulate(Replacements, _, (t, r) => Text.Replace(t as text, r{0} as text, r{1} as text)),

    hatte nichts gebracht.

    Aber mit deiner Variante klappt es nun perfekt !!!
     
Thema:

Power Query grottenlangsam

Die Seite wird geladen...
  1. Power Query grottenlangsam - Similar Threads - Power Query grottenlangsam

  2. PowerQuery und Summenblatt

    in Microsoft Excel Hilfe
    PowerQuery und Summenblatt: Hallo. Ich möchte eine Vorlage erstellen, für den Import von Budgetlisten. Die Gesamtübersicht ist so strukturiert, dass sich darauf alle Werte der Tabellenblätter befinden. Gleichzeitig werden...
  3. Datum/Uhrzeit runden in PowerQuery

    in Microsoft Excel Hilfe
    Datum/Uhrzeit runden in PowerQuery: Hallo zusammen, ich brauche eure Hilfe. Gibt es in PowerQuery eine Möglichkeit bei folgendem Format die Sekunden automatisch für die ganze Spalte auf 00 zu setzen? 24.01.2024 14:29:43 >>>...
  4. Tabelle strukturieren Angebote, Aufträge, Rechnungen, Buchung

    in Microsoft Excel Hilfe
    Tabelle strukturieren Angebote, Aufträge, Rechnungen, Buchung: Hallo Forum, ich benötige bitte eure Hilfe bezüglich des Strukturierung einer Tabelle, in die ich Angebote, Aufträge, Rechnungen und Buchungen eintragen möchte. Wahrscheinlich gibt es eine...
  5. Power Query - Rücklaufkontrolle und Laufzeit berechnen

    in Microsoft Excel Hilfe
    Power Query - Rücklaufkontrolle und Laufzeit berechnen: Liebe Power Query Enthusiasten Ich habe folgende Aufgabe: Für Versandboxen mit eindeutiger ID soll eine Rücklaufkontrolle und Berechnung der Laufzeit erstellt werden. Die entsprechenden Daten aus...
  6. Power Query - nested if statement - Klammern

    in Microsoft Excel Hilfe
    Power Query - nested if statement - Klammern: Hallo zusammen, kann mir jemand beantworten, in welchen Fällen bei nested if statements in Power Query Klammern notwendig sind und wann nicht? Beispiel: if [Number] > 0 then "Positive" else (if...
  7. Übergang zwischen Zeilenabschnitten verknüpfen

    in Microsoft Excel Hilfe
    Übergang zwischen Zeilenabschnitten verknüpfen: Hallo zusammen, ich habe Daten aus einer Ressourcenliste in einer Urlaubsliste verknüpft. Sinn dahinter ist, dass sobald sich Einträge in der Ressourcenliste ändern, diese in der Urlaubsliste...
  8. Excel Powerquery: Nach Schließen & Laden Fehlermeldung [DataFormat.Error]

    in Microsoft Excel Hilfe
    Excel Powerquery: Nach Schließen & Laden Fehlermeldung [DataFormat.Error]: Hallo zusammen! Ich bin gerade dabei von einem Teams-Sharepoint-Ordner Daten mit Power-Query abzurufen. Ich lade die Daten über "Daten Abrufen -> Datei -> Sharepoint-Ordner" und gebe dann den...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden