Filtrat einer Excel-Tabelle in Power Query importieren

Es gibt Sit­u­a­tio­nen, wo nur jene Dat­en in Pow­er Query importiert wer­den sollen, die in der Excel-Tabelle sicht­bar, also nicht ver­steckt oder aus­ge­blendet sind. Das „wieso„und das „warum” möchte ich hier nicht disku­tieren, ich nehme es ein­fach als gegeben hin. Der Wun­sch wurde so an mich herange­tra­gen. Damit Sie sich vielle­icht ein besseres Bild machen kön­nen und wir auch über die gle­iche Sache sprechen, laden Sie doch ein­fach ein­mal diese etwas ältere Datei mit den Mit­gliedern des 18. Deutschen Bun­destages, Stand Som­mer 2015 herunter. Um poli­tisch in jedem Fall kor­rekt zu bleiben hier meine Empfehlung: Fil­tern Sie die Tabelle der­art, dass alle Vor­na­men, welche mit einem „I” begin­nen, angezeigt wer­den. Im Fil­trat wer­den dann noch 10 Namen angezeigt:

Die in Excel gefilterten Dat­en

Das Ziel: Diese 10 gefilterten Zeilen sollen in eine Pow­er Query-Abfrage importiert wer­den. Und das soll natür­lich je nach Fil­terbe­din­gung der Quell­dat­en dynamisch geschehen. Sie ken­nen das Pro­cedere für den Import: Eine Zelle in den Dat­en markieren, Dat­en | Aus Tabelle/Bereich und die Dat­en wer­den in den Pow­er Query-Edi­tor geladen. Ja, aber nicht nur die. PQ inter­essiert sich nicht für einen in Excel geset­zten Fil­ter. Es wird immer die kom­plette Tabelle geladen.

Es bleibt der Wun­sch, dass nur das Fil­trat in die Abfrage über­nom­men wird, damit in PQ nicht noch (von Hand) gefiltert wer­den muss. Und gle­ich an dieser Stelle der Hin­weis: Pow­er Query kann das nicht, es wird immer die kom­plette Tabelle importiert. Aber ich würde diesen Beitrag nicht schreiben, wenn es nicht doch einen Ausweg gäbe. 😎

Been­den Sie erst ein­mal wieder die Abfrage per Schließen & laden oder Schließen & laden in… um die Abfrage entwed­er als Nur Verbindung zu sich­ern oder an definiert­er Posi­tion in die Arbeitsmappe zu schreiben. Sie befind­en sich und bleiben erst ein­mal auch in Plain Excel. Und das Wichtig­ste: Jet­zt kön­nen Sie hier auch an den Quell­dat­en erforder­liche Änderun­gen vornehmen. Dazu ent­fer­nen Sie zu Beginn den Fil­ter, damit wieder alle Vor­na­men und damit alle Daten­sätze sicht­bar sind.

Gehen Sie über Tabel­len­tools | Entwurf in die Gruppe Eigen­schaften und über das Sym­bol Tabel­len­größe ändern ver­bre­it­ern Sie den Bere­ich auf =$A$1:$D$643, also 1 Spalte mehr. Schreiben Sie in die Zelle D2 nun diese Formel: =AGGREGAT(3; 5; A2) und Sie wer­den erken­nen, dass in jed­er Zeile dieser Spalte der Wert 1 ste­ht. Das kön­nen Sie ganz leicht kon­trol­lieren, indem Sie die Über­schrift erweit­ern. – Aber ich bin Ihnen noch eine kleine Erk­lärung zu der AGGREGAT-Funk­tion schuldig:

• Das erste Argu­ment mit der Beschrei­bung des Argu­ments (Funk­tion) ist 3, was für die Funk­tion ANZAHL2() ste­ht. Es wird also ein beliebiger Wert in der Zelle gew­ertet.
• Mit dem zweit­en Argu­ment sollen die aus­ge­blende­ten Zeilen ignori­ert wer­den, also muss dort der Wert 5 eingegeben wer­den.
• Als let­ztes Argu­ment wird ein Array ver­langt; hier trage ich ein­fach die Adresse der Zelle A2 von Hand ein. Für mich ist das trans­par­enter als ein Klick auf A2, wo dann der Bezug auf die Intel­li­gente Tabelle einge­tra­gen wor­den wäre: Tabelle1[@Name].

Und jet­zt fil­tern Sie wieder die Tabelle, gerne wie gehabt nach dem ersten Zeichen des Vor­na­mens, sodass nur die entsprechen­den Vor­na­men, mit einem „I” begin­nend, angezeigt wer­den. Öff­nen Sie die Abfrage und auf den ersten Blick fällt auf, dass die Query eine Spalte mehr hat. Sollte das nicht der Fall sein, Klick­en Sie auf Aktu­al­isieren. Und auf den zweit­en Blick wer­den Sie erken­nen, dass zwar wieder alle 642 Zeilen geladen wur­den, aber in der neuen Spalte die Werte 0 und 1 vertreten sind. Fil­tern Sie nun in Pow­er Query diese Spalte1 nach dem Wert 1 und sofort erken­nen Sie, dass auss­chließlich die gle­ichen Dat­en auf dem Schirm sicht­bar (und auch vorhan­den sind) wie in dem Excel Arbeits­blatt.

So weit ist ja nun wirk­lich kein her­aus­ra­gen­der Vorteil zu sehen; außer der Tat­sache, dass diese Tabelle nach dem laden in Excel auss­chließlich diese Zeilen enthält und nicht durch Rück­gängig­machen der Fil­terung wieder alle Dat­en sicht­bar sind. Aber das wäre auch mit einem Kopieren des Fil­trats und ein­fü­gen in ein neues Blatt mach­bar gewe­sen, wenn diese neue, nur in Excel erstellte Tabelle als Daten­quelle genutzt wor­den wäre. Aber sehen Sie selb­st…

Löschen Sie nun ein­fach in der PQ-Abfrage die let­zte Spalte. Schließen Sie die Query und ändern Sie in der Excel-Tabelle den Fil­ter. Wählen Sie beispiel­sweise den Anfangs­buch­staben „A” für den Vor­na­men. Und bei der Gele­gen­heit kön­nen Sie auch gle­ich die Spalte D ver­steck­en, damit die Hil­f­ss­palte unsicht­bar ist. Wech­seln Sie wieder in die Pow­er Query-Abfrage und da typ­is­cher­weise beim Öff­nen keine automa­tis­che Aktu­al­isierung stat­tfind­et, ste­hen auch noch die bish­eri­gen Werte dort. Ein Klick auf Aktu­al­isieren und jet­zt sind in der Abfrage auch nur jene Werte enthal­ten, die im Work­Sheet sicht­bar sind. Das Ziel ist damit erre­icht. 💡 

▲ nach oben …