PDA

Vollständige Version anzeigen : Power Query - Sammelthema für bestehende Lösungen zu konkreten Aufgaben


ebs17
19.01.2019, 14:40
Power Query - was ist das?
Vortrag von Peter Rühm zur Access-Entwickler-Konferenz 20 (http://www.donkarl.com/Downloads/AEK/AEK20_PowerQuery.zip)

>> Power Query ist als Add-In für Excel 2010 und Excel 2013 verfügbar, in den Folgeversionen ist es direkt in Excel integriert.

>> Man kann damit richtige Datenbankarbeit direkt in Excel ausführen analog der Abfragen in Access. Ebenso gibt es eine grafische Bedienoberfläche, wo man sich Anweisungen "zusammenklicken" kann, ehe man sich dann für höhere Aufgaben der eigenen Programmiersprache Power Query Formula Language (M) (https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference) zuwenden muss, die auch viele Elemente von SQL-Anweisungen enthält.
Die Verarbeitung auch von sehr großen Datenmengen gelingt sehr performant.

>> Die Einbettung in Excel ist sehr überzeugend. Die Menüführung entspricht jener in Excel. Das Ergebnis einer PQ-Abfrage wird als datenbanktaugliche Tabelle in der Excelmappe abgelegt und kann unmittelbar weiterverwendet werden, z.B. für weitere Excelverarbeitung wie Pivottabelle und Diagramm oder auch Exporte.

>> Über Power Query Formula Language (M) sind komplexe Verarbeitungen in vielen Einzelschritten lösbar. In Excel selber läuft das ohne nötige Nutzung von VBA ab, was für einige Sicherheitsaspekte nicht unerheblich ist.

>> Power Query kann sich Daten aus unterschiedlichsten Quellen ziehen: Websites, Excel- oder CSV-Datei, XML-Datei, Textdatei, SQL Server-Datenbank, Microsoft Azure SQL-Datenbank, Access-Datenbank, Oracle-Datenbank, IBM DB2-Datenbank, MySQL-Datenbank, PostgreSQL-Datenbank, Sybase-Datenbank, Teradata-Datenbank, SharePoint-Liste, OData-Feed, Microsoft Azure Marketplace, Hadoop-Datei (HDFS), Microsoft Azure HDInsight, Microsoft Azure Tabellenspeicher, Active Directory, Microsoft Exchange Server, Facebook

>> Integriert sind Funktionalitäten, die man sich anderenorts erst etwas umfangreicher herleiten und programmieren muss wie
- Unpivot: Erstellen einer db-tauglichen Tabelle aus Pivottabellen, Matrixen.
- Auslesen der Dateien aus einem Dateisystem

Wer sich also sowohl in der Datenbankwelt wie auch in der Excelwelt bewegt und beide in seinen Lösungen verbinden möchte bzw. muss, findet hier ein hochinteressantes Feld. Eine Beschäftigung damit ist also lohnend.

Dieses Thema hier soll als Linksammlung dienen, um Lösungen aus dem Bereich Power Query zusammenzufassen und so einem Suchenden eine erste Hilfe anbieten zu können. Ein Beitrag hier im Thema enthält also wünschenswerterweise nur zu einer konkreten Lösung eine Kurzbeschreibung der Aufgabe und den Link zur erfolgreichen Umsetzung.
Mit Lösungen sind hier explizit bestehende Lösungen gemeint, nicht solche, die erst noch zu ermitteln wären.

Einzelne konkrete Fragen sollen nach wie vor in einem eigenen Thema gestellt, diskutiert und geklärt werden, diese sind hier unerwünscht, weil sie nur den beabsichtigten Übersichtscharakter an dieser Stelle verwässern und zerstören.

ebs17
19.01.2019, 14:44
Häufigste Duos herausfinden
Für unseren Sportverein und unsere Trainings führe ich eine Excel-Anwesenheitsliste. In der ersten Spalte stehen die Namen und in den folgenden Spalten trage ich jeweils die Anwesenheit ein. Jede Spalte ist ein anderes Datum. Wer anwesend war, erhält in seiner Zeile in der jeweiligen Datums-Spalte eine 1, wer nicht dabei war eine 0. Nun möchte ich herausfinden, welche zwei Personen zusammen am häufigsten anwesend waren, Also als Beispiel: wir haben drei Personen: Person A war 23 Mal dabei, Person B 27 Mal und Person C 18 Mal. B war 12 Mal im Training, wenn C im Training war, A war 14 Mal im Training, wenn C im Training war und A war 19 Mal im Training, wenn B im Training war. Einige waren auch z. T. alleine am trainieren. Somit wären A und B das häufigste Duo. Gibt es eine Möglichkeit, dies mit einer Formel herauszufinden?
=> Link zur Lösung (https://www.ms-office-forum.net/forum/showpost.php?p=1903394&postcount=4)

WS-53
20.01.2019, 09:39
Hallo,

ja Powerquery ist tatsächlich sehr leistungsfähig. Sind aber die Fragestellungen etwas komplexer, dann ist es im Moment noch schwierig, schnell die richtige Antwort zu finden.

Beispielsweise ist ja eine durchaus realistische Idee, dass die Quelle einer Abfrage variabel gehalten werden soll. Genauso realistisch ist es, dass bei Abfragen, die eine große Datenmenge liefern können, bereits in der Abfrage nach Kriterien gefiltert wird, die von außen vorgegeben werden können.

Hierzu hat Andreas Thehos auf youtube den Power Query Workshop 1 mit den Teilen 01-04 eingestellt.

Power Query Workshop 1/01 - Übersicht Parameter (https://www.youtube.com/watch?v=yehUqx-Tum0)
Power Query Workshop 1/02 - Dateien einlesen (https://www.youtube.com/watch?v=lqa9WErBZOU)
Power Query Workshop 1/03 - Kontrollierte Abfrage (https://www.youtube.com/watch?v=VM7jbl3Y-2M)
Power Query Workshop 1/04 - Abfragen aktualisieren (VBA) - dynamisch mit Funktion
(https://www.youtube.com/watch?v=ya2_xfUX-1Ehttp://)

Es war zwar etwas umständlich (Video mehrfach vor und zurückspulen), aber letztendlich habe ich Teil 04, der mir aktuell gerade wichtig war, erfolgreich nachbauen können.

Diese Videos sind stand heute nicht in der Videoübsicht auf der Seite von Andreas Theos (https://thehosblog.com/weitere-themen/videoliste/) aufgeführt

Frank Furter
26.01.2019, 23:00
Hallo,
...
Es war zwar etwas umständlich (Video mehrfach vor und zurückspulen), aber letztendlich habe ich Teil 04, der mir aktuell gerade wichtig war, erfolgreich nachbauen können...


Hallo, und dieses Nachgebaute (Wissen) behälst du jetzt für dich? Dann hättest du diesen Part doch nicht erwähnen sollen... Oder lässt du andere teilhaben?

WS-53
31.01.2019, 09:43
Hallo,

ich habe nun mein nachgebautes Beispiel noch einige male getestet und die notwendigen Schritte, stichwortartig dokumentiert.

Somit kann ich dieses nun hochladen und der Allgemeinheit zur Verfügung stellen.

Was mich übrigens etwas stört, ist dass wenn ich nach der Aktualisierung einer Abfrage, bspw. die Breite von Spalten anpasse, dann gehen diese Anpassungen mit der nächsten Aktualisierung wieder verloren.

Frank Furter
31.01.2019, 20:30
Hallo, ich schaue mir das später genauer an, danke für die Arbeit.
Das, was dich etwas stört, lässt sich leicht beheben..:

Klicke in deine AusgabeTabelle. Dann auf Reiter Daten --> Eigenschaften und dort den Haken rausnehmen..:

ebs17
31.01.2019, 21:25
@Frank Furter:
Kannst Du lesen? Dabei auch Fettschrift wahrnehmen?
Bitte Beitrag #1 respektieren.

Hier soll nicht der Raum sein für Kommentare, Einzelfragen, Diskussionen!
Dazu kann man sich woanders austoben.
Hier sollen schlicht LÖSUNGEN gesammelt werden.

WS-53
27.02.2019, 22:54
Sollen innerhalb von Power Query Werte in Spalten ersetzt werden, dann gibt es dafür ja die Funktion TableReplaceValue, die im Start-Menü vo PQ zur Verfügung steht. Da können dann natürlich auch mehrere bis viele Ersetzungen definiert werden. Um diese Ersetzungsmethode zu nutzen, muss diese nur angeklickt und das Popup ausgefüllt werden. Ist also ganz einfach.

Aber spätestens bei vielen Ersetzungen, die auch noch pflegbar sein sollen, stellt sich dann bei den meisten die Frage, obe nich auch eleganter geht.

Und hier ist die Antwort ein eindeutiges JA, es geht eleganter. Bei meiner Suche nach einer eleganteren Möglichkeit, bin ich dann zuerst auf Chris Webb´s Blog gestoßen. Die von ihm vorgestellte Methode funktioniert, ist aber sehr langsam.

Weiter unten im Beitrag habe ich dann aber eine sehr schnelle Methode gefunden, die von Brandon beigesteuert wurde.

Durch die sehr freundliche und kompetente Unterstützung von Ignaz Schels (Fachbuchautor), habe ich nun eine Mustermappe, die verschiedene Möglichkeiten aufzeigt. Diese stelle ich deshalb nun hier zur Verfügung.

WS-53
28.02.2019, 16:23
Heute kam die Frage hoch: Wie können bei einem CSV-Import auch die Metadaten in die Abfrage übernommen werden.

Es gibt wohl mehrere Möglichkeiten. Eine von mir gefundene scheint aber nur unter Power BI zu funktionieren. Ander wurde erwähnt, aber eine Demomappe nicht bereitgestellt.

Ich habe auch eine Lösung gefunden (Diesmal ohne fremde Hilfe) und dazu die Ergnisse hochgeladen.

Hier der Link (https://www.ms-office-forum.net/forum/showthread.php?t=358501)

WS-53
06.03.2019, 14:55
Hallo,

in einer Abfrage soll die chronologische Entwicklung von diversen Werten gezeigt werden. Dazu gibt es täglich eine Mappe, die aber nur die Werte des betreffenden Tags bzws des Vortags enthält.

Post 21 des folgenden Links enthält eine Musterlösung. (https://www.ms-office-forum.net/forum/showthread.php?p=1918644#post1918644)

Dabei bin ich froh, diese Lösung gefunden zu haben, will aber nicht ausschliessen, dass es auch viel bessere Lösungen geben kann.