MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > Microsoft Access - Code Archiv
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 19.01.2019, 14:40   #1
ebs17
MOF Guru
MOF Guru
Standard Info - Power Query - Sammelthema für bestehende Lösungen zu konkreten Aufgaben

Power Query - was ist das?
Vortrag von Peter Rühm zur Access-Entwickler-Konferenz 20

>> 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) 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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}:{11}:{12} <= geklammerte Zahlen sind Einzelthemen
Dein Dankeschön: DBWiki => Spende

Geändert von ebs17 (19.01.2019 um 15:41 Uhr).
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.01.2019, 14:44   #2
ebs17
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

Häufigste Duos herausfinden

Zitat:

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

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}:{11}:{12} <= geklammerte Zahlen sind Einzelthemen
Dein Dankeschön: DBWiki => Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 20.01.2019, 09:39   #3
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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
Power Query Workshop 1/02 - Dateien einlesen
Power Query Workshop 1/03 - Kontrollierte Abfrage
Power Query Workshop 1/04 - Abfragen aktualisieren (VBA) - dynamisch mit Funktion


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 aufgeführt

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<
<FONT SIZE="1">
Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.

Geändert von WS-53 (20.01.2019 um 09:52 Uhr).
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 26.01.2019, 23:00   #4
Frank Furter
MOF Koryphäe
MOF Koryphäe
Standard

Zitat: von WS-53 Beitrag anzeigen

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?

__________________

gruß vom Frank Furter mit hiob's botschaften
Frank Furter ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 31.01.2019, 09:43   #5
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.
Angehängte Dateien
Dateityp: xlsx Excel PQ - zu öffnende Mappe mittels Parameter und Funktion bestimmen.xlsx (21,1 KB, 9x aufgerufen)
Dateityp: xlsx Kunden_1.xlsx (8,8 KB, 7x aufgerufen)
Dateityp: xlsx Kunden_2.xlsx (8,9 KB, 6x aufgerufen)

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<
<FONT SIZE="1">
Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 31.01.2019, 20:30   #6
Frank Furter
MOF Koryphäe
MOF Koryphäe
Standard

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..:
Angehängte Grafiken
Dateityp: jpg spaltenbreite.JPG (67,0 KB, 20x aufgerufen)

__________________

gruß vom Frank Furter mit hiob's botschaften
Frank Furter ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 31.01.2019, 21:25   #7
ebs17
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

@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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}:{11}:{12} <= geklammerte Zahlen sind Einzelthemen
Dein Dankeschön: DBWiki => Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 27.02.2019, 22:54   #8
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.
Angehängte Dateien
Dateityp: xlsx Excel PQ - Inhalt mehrerer Spalten mittels List.Buffer ersetzten.xlsx (89,7 KB, 7x aufgerufen)

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<
<FONT SIZE="1">
Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 28.02.2019, 16:23   #9
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<
<FONT SIZE="1">
Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 06.03.2019, 14:55   #10
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.

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

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<
<FONT SIZE="1">
Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 04:45 Uhr.



Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.