Category Archives: Power Query

Eine Glatze ist FKK auf höchster Ebene.

Power Query bei Ken Puls zu lernen ist ein Genuss.

Teil II

Auch deshalb (aber nicht nur deshalb), weil er auch so gerne über Excel, Ungereimtheiten und Schwächen lästert. Hier eine kleine Auswahl seiner spöttischen Bemerkungen über Power Query & co:

Der Dialog „gruppieren nach“ ist klasse – aber er zeigt die drei Pünktchen erst dann, wenn man mit der Maus darüber fährt.

Importiert man einen Bereich nach Power Query wird daraus eine Tabelle!?! Der Name der Tabelle: Tabelle1!

to do: es diesmal nicht schon wieder verkacken!

Power Query bei Ken Puls zu lernen ist ein Genuss.

Auch deshalb (aber nicht nur deshalb), weil er auch so gerne über Excel, Ungereimtheiten und Schwächen lästert. Hier eine kleine Auswahl seiner spöttischen Bemerkungen über Power Query & co:

Warum ist bei Dialogen so häufig der Default-Button derjenige, der am unwichtigsten ist? Kens Tipp: meistens ist der Button links von der Abbrechen-Schaltfläche der wichtige:

„Gebietsschema“: Der langsamste Dialog in PQ:

Warum ist das „schnelle Laden von Daten“ nicht Standard?

Warum zeigt ein Doppelklick auf den Rand nicht den kompletten Inhalt?

Ich hab doch gesagt, ich mach’s. Da muss ich nicht vierteljährlich dran erinnert werden …

Ich habe meine Verwunderung darüber ausgedrückt, dass PowerQuery (ein Werkzeug, das ich wirklich schätze und das sehr stark und mächtig ist), nicht druckbare Zeichen nicht darstellt. Das rief Kritik auf den Plan. Von vorne. Gegeben sei eine Tabelle, die von einem anderen System geliefert wurde, in denen nicht druckbare Zeichen vorhanden sind:

Die Funktionen CODE und ZEICHEN, TEIL und WECHSELN helfen in Excel beim Säubern, beziehungsweise beim Trennen der Daten.

Ziehe ich die Daten nun nach Power Query, so mein Erstaunen, werden diese Zeichen dort nicht angezeigt. Auch der Assistent „Spalten teilen“ biete keine Option für „nicht druckbare Zeichen“. Beim Zurückspielen nach Excel sind diese Zeichen wieder vorhanden (sie wurden ja nie gelöscht):

Als ich etwas leichtfertig und zugegebenermaßen nicht ganz korrekt, gepostet habe, dass PQ das nicht kann, rief ich Kritik auf den Plan:

„Hallo René,
ich hoffe es geht Dir gut. Ich weiß zwar nicht genau, was Du mit nichtdruckbaren Zeichen im Detail in Excel machst, aber ich bin mir ziemlich sicher, dass das – entgegen Deiner Bemerkung – auch mit Power Query geht“

„Das ruft den Experten auf den Plan, wenn ich behaupte, dass das nicht mit PQ geht …
Hallo Lars,
es gibt Systeme, die liefern in Excel oder Textdateien nicht-druckbare Zeichen (die dort – in anderen Systemen – als Trennzeichen definiert sind)
Mit ist aufgefallen, dass PQ diese nicht anzeigt – aber – wenn ich die transformierte Datei wieder zurückspiele – diese Zeichen wieder drin sind.
schau mal; probier mal – korrigiere mich – lasse ich gerne!“

„Was Power Query nicht alles kann:

Als Hintergrund: Wenn Du die Daten in Power Query lädst, dann sind die nicht druckbaren Sonderzeichen zwar nicht (ohne weitere Arbeit) sichtbar, aber sie sind vorhanden und man kann sich „um sie kümmern“
Ich denke, dass ich dazu mal einen Blogbeitrag schreiben werde. Danke für die Datei und diese Herausforderung“

„Hallo Lars,
ja – DAS kann ich auch:

let

    Source = Excel.CurrentWorkbook(){[Name=“Tabelle1″]}[Content],

    #“Changed Type“ = Table.TransformColumnTypes(Source,{{„Name“, type text}}),

    #“Name getrennt“ = Table.AddColumn(#“Changed Type“, „Name getrennt“, each Text.Replace([Name],Character.FromNumber(7),“|“))

in

    #“Name getrennt“

Ich hätte es schön gefunden, wenn das mächtige Power Query in seinem Assistenten „Spalte teilen“ eine Option dafür gehabt hätte …“

„Hi Rene,

okay, aber in Excel benutzt Du dafür doch auch Formeln, wieso ist das für PQ dann nicht erlaubt? Die Engine hat die Fähigkeiten, aber das Dev Team hat über die GUI eben noch keinen Befehl bereitgestellt. 

Aus Deinem Post hatte ich verstanden, dass es gar nicht geht, nicht, dass es nicht über die GUI geht.  Das finde ich nicht besonders schlimm.“

„Okay, Lars, du hast gewonnen.
Ich habe den Satz korrigiert:
„Übrigens: bedauerlicherweise kann man dies nicht mit Power Query mit den „Hausmitteln“ trennen – man benötigt hier einige Zeilen M.“
Ich war verblüfft, dass die nicht druckbaren Zeichen nicht angezeigt werden, aber (und das ist eigentlich auch vernünftig) nicht gelöscht werden.
Ich hätte mir in dem (sehr viel mächtigeren Assistenten als in Excel) „Spalten teilen“ eine Option gewünscht, wo man Character.FromNumber() (oder ähnliches) eintragen kann.
Tja. Liebe Grüße Rene“

„Meine Funktion tut genau das… Ich habe sie recht schnell entworfen, daher muss der ReplacerText auch als Unicode-Zeichen (also als Nummer) eingegeben werden, anstatt als Text… könnte man alles noch verbessern, aber sie tut bisher, was sie soll…

(TextMitNonPrintables as text, optional ReplacerText as number) as text =>

let

/*

   TextMitNonPrintables = „Lars “ & Character.FromNumber(7) &“Schreiber“,

   ReplacerText = null,

*/

    //Falls der ReplacerText nicht mit übergeben wurde, setze ihn aufs Leerzeichen

    RepText = if ReplacerText = null then 32 else ReplacerText,

    //Nicht druckbare Unicode-Zeichen als Liste definieren…

    NichtDrurckbareZeichenUnicode = {0..31},

  //Nicht-druckbare Unicode-Zeichen durch den ReplacerText ersetzen…

    TextAsList = List.Transform(

            Text.ToList(TextMitNonPrintables),

            each Character.FromNumber(

                if List.Contains(NichtDrurckbareZeichenUnicode, Character.ToNumber(_)) then

                  RepText

                else

                Character.ToNumber(_)

                )

                ),

      //Text-Liste wieder in Worte zusammensetzen und um unnötige Leerzeichen bereinigen!

      Output = Text.Trim(

        Text.Combine(TextAsList

        )

        ) 

in

    Output

Lars Schreiber

Ein Träumchen

Daumen hoch für Johannes Curio (http://curio-consulting.de/), der auch als Referent bei unseren Exceltagen zur Verfügung stand. Er hielt informative, amüsante und spannende Referate über Pivot, Power Query und PowerBI.

Spannend fand ich seine Bemerkung, dass man in PowerQuery niemals den Automatismus „Changed Type“ verwenden sollte. In vielen Fällen wird der Datentyp nicht richtig erkannt, so seine Bemerkung.

Dies demonstrierte er anhand eines CSV-Imports am Beispiel einer Datumsspalte. Seine Empfehlung: diesen Schritt löschen und selbst das Datenformat definieren:

Dann klappt es:

Historisch gesehen müssten wir heute in der Zukunft leben.

Rückblick Exceltage 2019. Mit Imke Feldmann konnten wir eine hervorragende PowerBI-Programmiererin und -kennerin finden. Warum erscheint sie nicht bei der Suche nach PowerBi-Fragen? Ihr Blog https://www.thebiccountant.com/ ist auf Englisch und deshalb wird sie Deutschland nur schwer gefunden. Dennoch: Kenner der Szene kennen und schätzen sie sehr. Ich habe sie das erste Mal live erlebt und war begeistert von ihrer lebendigen und witzigen Art, aber auch von ihrem profunden Sachwissen und ihrer Kompetenz, Probleme mit Daten zu lösen.

Amüsiert habe ich mich über ihr Erstaunen, dass links neben den PowerQuery-Befehlen Zeilennummern (besser: Befehlsnummern) stehen.

Man kann diese Befehle im Erweiterten Editor ein- und ausschalten: In den Anzeigeoptionen gibt es die Einstellung „Zeilennummern anzeigen“.

M

Für unsere Exceltage 2019, die in München am 18. und 19. Oktober stattfinden, konnten wir auch den hervorvorragenden „Power“-Spezialisten Hans-Peter Pfister gewinnen. Er wird über PowerQuery, Power Pivot, Power BI und M sprechen.

In seinem Skript über die Abfragesprache M finde ich folgenden wichtigen Satz:

Mit Eingabe des Kommas wird die Variablendefinition abgeschlossen. Einzige Ausnahme ist die letzte Variablendefinition vor in – hier darf nie ein Komma stehen.

Wie oft bin ich schon darüber gestolpert, dass ich – Macht der Gewohnheit – hier ein Komma eingetragen habe.

Gehe ins Bett! Begib dich direkt dorthin! Gehe nicht in die Küche! Ziehe nichts Süßes mehr ein!

Schade! Wenn man mit Power Query auf eine Liste zugreift und diese in Excel als Tabelle einfügt, kann man sie aus dem Aufgabenbereich „Abfragen und Verbindungen“ in einer „anderen Form“ laden – der Dialog „Daten importieren“ wird geöffnet.

Dieser Dialog kann auch über das Symbol „Laden in“ aus der Registerkarte „Abfrage“ der „Abfragetools“ geöffnet werden:

Jedoch leider nicht aus dem Power Query-Editor, wenn er einmal geschlossen wurde und dann wieder geöffnet wurde:

Warum ist das kleine L fast immer größer als das große l?

Schade. Ich liebe Power Query! Gut, durchdacht, clever, scheinbar fehlerfrei. Jedoch: eine Sache habe ich gefunden, die mich sehr irritiert.

Importiert man eine XML-Datei, in der sich Umlaute befinden, werden diese nicht korrekt angezeigt und lassen sich auch noch transformieren. Ich habe weder einen Schalter (Gebietsschema) noch einen anderen, cleveren Ersetzen-Befehl gefunden. Schade!

Ich esse Fleisch, weil mir die kleinen Tofus so leid tun.

Die Aktion kann nicht abgeschlossen werden, da die Datei in Microsoft Mashup Evaluation Container geöffnet ist.

Diese lustige Meldung habe ich erhalten, als ich versucht habe eine Datei umzubenennen. Was habe ich gemacht? Ich habe mit Power Query in Excel auf die Datei zugegriffen und dann den Power Query Editor verlassen und die Änderungen nicht beibehalten. Im Fenster „Abfragen und Verbindungen“ wird keine Verbindung angezeigt, dennoch hält das “ Microsoft Mashup Evaluation Container“ diese Datei.

Je mehr du wiegst umso schwerer kannst du entführt werden. Schütz dich und esse Kuchen!

Heute fragte ein Teilnehmer in der Excelschulung, ob man eine Filterung „umkehren“ kann. Also das Komplement anzeigen kann.

Gute Frage – ich wüsste keine einfache Antwort. Hilfsspalte?!? Spezialfilter?!?

In Power Query und M ist dies kein Problem:

„Filtered Rows“ = Table.SelectRows(#“Changed Type“, each [#“Kategorie-Nr“] <> 2 and [#“Kategorie-Nr“] <> 4 and [#“Kategorie-Nr“] <> 6 and [#“Kategorie-Nr“] <> 8)

Auch die schwärzeste Stunde hat nur 60 Minuten.

Hallo Angelika,

das Werkzeug heißt „Daten abrufen und transformieren“. Und darum geht es – nicht um das Formatieren:

Ich erstelle eine Verknüpfung zur Nordwinddatenbank und lade beispielsweise die Tabelle „Rechnungen“ in den Power Query-Editor. In den letzten beiden Spalten befinden sich Zahlen > 1000. Ich wandle sie in Text um.

Beispielsweise 1113,75

Konvertiere ich diesen Text nun in eine Dezimalzahl nach dem englischen Gebietsschema (US) um, so erhalte ich 111375.  Das Komma wäre in den USA als Tausendertrennzeichen gedacht; macht keinen Sinn – wird entfernt.

Letzten Schritt löschen.

Ich konvertiere den Typ in Dezimalzahlen Gebietsschema Deutsch (Deutschland) und erhalte nun 1113,75.

Es geht beim Konvertieren nicht um die Frage: ich möchte diese Zahl US-amerikanisch oder deutsch darstellen, sondern ich erhalte eine solche Zahl (oder Datum) und möchte sie so transformieren, dass mein System es verarbeiten kann.

Die Darstellung wird dann in Excel durch Formatieren erledigt. Oder durch die Einstellungen des Betriebssystems, bzw. von Excel.

Ich bin nicht oberflächlich, ich hab auch hässliche Freunde!

Gestern Abend auf dem Excelstammtisch. Wir diskutieren über Listen und über verschiedene Möglichkeiten sie zu vergleichen. Beispielsweise mit Power Query (Daten abrufen und transformieren). Man muss nicht nur eine Spalte verwenden (die ID), sondern kann auch mehrere Spalten als Primärschlüssel verwenden. Man muss sie markieren. Ich versuche es – padautz – es geht nicht!

Die Lösung ist schnell gefunden: Ich habe in der ersten Liste Spalte 1 und dann Spalte 2 markiert, in der unteren dagegen Spalte 2 und anschießend Spalte 1.

Die Fehlermeldung ist merkwürdig: „Wählen Sie Spalten desselben Typs aus, um den Vorgang fortzusetzen.“ Habe ich doch!

Wenn man genau hinschaut, kann man die Nummer der Reihenfolge als Beschriftung in der Spalte sehen:

Also: richtig markieren – dann darf ich auch verknüpfen: