Category Archives: Power Query

Habe meinen Wecker heute auf die Arbeit mitgenommen. Damit er sieht, wozu er mich jeden Tag nötigt.

Irre!

Wenn man mit PowerQuery auf eine „ältere“ XLS-Excelmappe zugreift, werden folgende Spalten angezeigt:

Beim aktuellen Dateiformat XLSX dagegen drei weitere:

Die drei Spalten (mit ihren Informationen) Item, Kind und Hidden fehlen.

Würde man eine XLS-Datei in XLSX umbenennen, wäre das Ergebnis das Gleiche wie bei XLS:

Wer macht denn so etwas? Und: DAS würde man in Excel doch sofort bemerken.

Ich erhalte vorgestern die Frage, warum das PowerQuery-Tool, das ich für die Firma erstellt habe bei einer Datei nicht läuft. Ich stelle fest – obwohl die Datei vom Format XLSX ist, werden nur die beiden Spalte Name und Date angezeigt, nicht jedoch Item, Kind und Hidden. Warum?

Ich gehe auf die Suche.

Ich stelle fest, dass die im openXML-Format verwendete interne Datei app.xml (im Ordner docProps) folgendermaßen aussieht:

Wenn ich die Datei, die von Apache POI erstellt wurde, öffne, speichere und schließe, sieht diese XML-Datei so aus:

SO jetzt jede XLSX-Datei aus, die von Excel erzeugt und in Excel gespeichert wurde.

Das bedeutet: das (umstrittene) Werkzeug Apache POI produziert XLSX-Dateien, die nicht genau der Spezifikation von Microsoft entsprechen. Ist das schlimm?

Ja, weil mein Werkzeug auf die Spalte „Kind“ zugreift und diese nicht findet …

Die gute Nachricht: ich bekomme den oberen Knopf meiner superengen Jeans endlich wieder zu. Die schlechte Nachricht: Ich habe sie nicht an.

In Excel erstellt man einen Zeilenumbruch in einer Zelle mit der Tastenkombination [ALT] + [Enter]. Der dahinterliegende Code hat die Nummer 10. Importiert man eine Liste mit Zellen mit Zeilenumbrüchen nach PowerQuery, sieht man schnell, dass #(lf), also Linefeed diesem Zeichen entspricht.

Aha, denke ich mir: es wäre doch schön, wenn eine Liste nicht mit Semikola getrennt wären:

Ich öffne den Dialog und trage statt Semikolon in der Kategorie „benutzerderfiniert“ #(lf) ein;

Das Ergebnis irritiert:

PowerQuery hat den Code geändert in:

= Table.TransformColumns(#"Grouped Rows", {"Anzahl", each Text.Combine(List.Transform(_, Text.From), "#(#)(lf)"), type text})

Natürlich könnte man es per Hand ändern in „#(lf)“

Oder durch die Funktion

Character.FromNumber(10)

ersetzen:

= Table.TransformColumns(#"Grouped Rows", {"Anzahl", each Text.Combine(List.Transform(_, Text.From), Character.FromNumber(10)), type text})

Schließlich entdecke ich, dass in der Kategorie „benutzerdefiniert“, dass man die Texte „mithilfe von Sonderzeichen verketten“ kann. Und dort findet sich auch der Zeilenvorschub. Und dieses #(lf) wird von PowerQuery auch nicht geändert.

Der Computer sagt „Drucker suchen“ und ich stelle mir vor, wie die Daten rumlaufen und panisch rufen „Ein Drucker! Ist ein Drucker anwesend?“

Hallo Herr Martin,

ich habe die Formel so angepasst, dass auf die richtigen Werte zugegriffen wird (d.h. es wird der Zinssatz verwendet, der als Drilldown-Liste angelegt wurde, s.u.). Leider kommt immer wieder eine Fehlermeldung, die ich nicht lösen konnte, auch nicht durch eine Internetrecherche. Den Code habe ich mit Notepade++ zusammengebaut und Ihnen angehängt. Das ist der Fehler (beim Komma):

Hallo Frau I.,

1. PowerQuery unterschiedet zwischen Groß- und Kleinschreibung.

Der Befehl lautet

Number.Power

(groß „N“, groß „P“)

2. Stimmt – ist mir später aufgefallen – ich habe die zweite Formel (P) vergessen. Und: NEIN: es gibt keine Barwert- oder andere finanzmathematische Funktion in PowerQuery

Liebe Grüße

Rene Martin

Nachtrag:

so könnte die Lösung aussehen:

Starten Sie den PowerQuery-Abfrageeditor.

Klicken Sie auf die Funktion fnBarwert.

Klicken Sie auf Ansicht / Erweiterter Editor.

Dort sehen Sie die Berechnung:

let 

    Barwert = (Zins as number, Restnutzdauer as number) as number =>

    let

        q = 1 + Zins / 100,

        Ergebnis = (Number.Power(q , Restnutzdauer) - 1) / (Number.Power(q , Restnutzdauer) * (q-1))

    in

        Ergebnis

in Barwert

Ist der Zins bei Ihnen eine Zahl oder eine Prozentzahl – also 3 oder 3%? Ist die Restnutzungsdauer in Jahren oder Monaten?

Tragen Sie einfach die entsprechenden Zahlen links ein – rechts wird der Barwert berechnet. Korrekt?

Liebe Grüße

Rene Martin

Findest du, dass ich zu wenig Busen habe. – Nein: zwei sind okay!

Lustige Frage in der letzten PowerQuery-Schulung:

Wenn ich den Datentyp über das Gebietsschema ändere (beispielsweise Englisch (USA), habe ich die Möglichkeit mit einem Klick auf Zahnrad-Symbol hinter dem Schritt den Schritt zu ändern:

Wenn allerdings für sehr viele Spalten der Datentyp geändert wurde, gibt es für DIESEN Schritt kein Zahnradsymbol. Was macht man nun, wenn einer der Datentypen einer Spalte falsch ist?

Die einfachste Möglichkeit: Man markiert die Spalte und ändert den Datentyp in den richtigen Typ. Dann wird dieses Element ersetzt.

Natürlich kann man auch in der Bearbeitungsleiste den Teil per Hand korrigieren:

Oder: man löscht den ganzen Schritt und erstellt ihn neu. Letztere Variante ist natürlich wenig sinnvoll …

Lust auf Vollkornnudeln? – Warum fängst du jetzt Streit an?

Lieber Herr Martin,

haben Sie vielen Dank, das hat prima funktioniert!

Nun soll aber im Tabellenblatt ein Wert manuell eingegeben werden, auf den dann die Berechnung der Spalte zugreift (ähnlich der Eingabe bei den Filterkriterien). Geht das?

#####

Hallo Frau I.,

Wenn Sie Werte auslagern möchten, dann „ziehen“ Sie die Daten nach PowerQuery, wählen den korrekten Datentyp (Text oder Zahl) und machen ein Drilldown, so dass nur noch ein Wert übrig bleibt.

Erstellen Sie eine neue Spalte, rechnen dort zuerst mit einem „harten“ Wert (beispielsweise +5) und ersetzen dann die zahl durch Ihre Variable (hier: + tbl_Plus)

Hilft das?

Hallo Herr Martin,

haben Sie vielen Dank!

Ich habe ein bisschen damit herum probiert. Sofern ich bei einer Tabelle neue Spalten aus derselben Tabelle hinzufüge, klappt alle prime. Allerdings erhalte ich einen Fehler beim Hinzufügen einer Spalte in die Tabelle qry_Datenzugriff, die sich berechnen soll aus „Bodenwert“ (Spalte mit vielen Werten der Tabelle qry_Datenzugriff) mal „Zinssatz“ (Spalte der Tabelle tbl_Zinssatz mit nur einem Wert). Wie kann ich das lösen?

Hallo Frau Issel,

die ersten beiden Schritte sind richtig: Sie laden die Tabelle nach PowerQuery; Sie wandeln den Typ in Dezimalzahl (oder Prozentzahl) um.

Aber dann fehlt der Drilldown: Sie müssen den Wert der Zelle in einen Wert verwandeln. Klicken Sie mit der rechten Maustaste auf die Zelle und führen den Drilldown durch:

Das Ergebnis sieht so aus:

Kann in einer Zeile geschrieben werden:

= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="tbl_Zinssatz"]}[Content],{{"Zinssatz", type number}}){0}[Zinssatz]

Und diese lange Formel können Sie nun in Ihrer Berechnung verwenden, also statt:

Table.AddColumn(#“Geänderter Typ“, „angemessener Zins“, each [#“Bodenwert €“]* 1.3)

Schreiben Sie:

Table.AddColumn(#“Geänderter Typ“, „angemessener Zins“, each [#“Bodenwert €“]* Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="tbl_Zinssatz"]}[Content],{{"Zinssatz", type number}}){0}[Zinssatz] )

Klappt das?

Liebe Grüße

Rene Martin

Die Frau hat einen Beutel: „Heute Rettich ich die Welt.“ Ich lese das und zucchinerlich zusammen.

Lieber Herr Martin,

haben Sie vielen Dank, das hat prima funktioniert!

Nun habe ich eine neue Herausforderung: ich möchte weitere Spalten mit unterschiedlichen Berechnungen hinzufügen, bei denen neue Spalten auf Werte von zuvor hinzugefügte Spalten zugreifen. Das sollte möglich sein, vermute ich.

#####

Hallo Frau Issel,

zu Ihren Fragen: klar können sie mit einer berechneten Spalte weiterrechnen: Sie fügen eine benutzerdefinierte Spalte ein: MWST = [Netto] + 0.07

und rechnen damit weiter:

Brutto = [MWST] + [Netto]

Da ist ein Fehler auf der Speisekarte. Sie haben „Rucola“ geschrieben. Da fehlt das „m“.

Guten Tag Herr Martin,

ich möchte aus den Werten von 2 Spalten Werte für eine 3. Spalte berechnen lassen und damit dann weiter rechnen. Kann ich das in PowerQuery erreichen?

Bspw. im Dokument Report09f.xlxs die Werte der Spalte K mal die der Spalte L. Anschließend möchte ich darüber Min, Max, Mittelwert berechnen, analog zu den Spalten, die im Exportdokument schon vorhanden sind.

Viele Grüße,

####

Hallo Frau I.,

in PowerQuery können Sie über „Spalte hinzufügen“ / Benutzerdefinierte Spalte eine Berechnung hinzufügen. Geben Sie dort den Namen der neuen Spalte an und die Berechnung, indem Sie auf diese langen Feldnamen doppelklicken!

Diese Spalte wird ans Ende der Tabelle gesetzt; Sie können sie schnell (über das Kontextmenü) an den Anfang verschieben:

Und dann per Hand etwas nach rechts:

Für die Aggregatfunktionen: erstellen Sie einen Verweis auf die Tabelle

Markieren die Spalte und wählen aus Transformieren / Statistiken die gewünschte Funktion aus.


Hilft das?
Liebe Grüße

Rene Martin

Hab gerade zwei Männer im Partnerlook gesehen und sie höflich gefragt, ob sie schwul sind. Mich dafür verhaften, finde ich schon krass …

Irgendwie doof!

In PowerQuery-Schulungen empfehle ich die Option „Spaltentypen und -überschriften für unstrukturierte Quellen niemals erkennen.“ einzuschalten. Warum? Beim Import von Textdateien und CSV-Dateien werden Datumsinformationen in Zahlen konvertiert, wie folgende Screenshots zeigen:

Diese Option steht in Microsoft 365 – jedoch nicht in Excel 2016 zur Verfügung:

Allerdings: in einem Ordner befinden sich eine Reihe gleichförmig aufgebauter Excelmappen:

Greift man mit PowerQuery auf den Ordner zu und lässt sich den Content anzeigen, dann wird die erste Zeile nicht als Überschrift erkannt und in die Liste eingefügt:

Natürlich kann man die erste Zeile zur Überschrift machen und die übrigen Zwischenzeilen löschen. Ist aber nervig. Was tun?

Ich weiß es nicht?

Sich an der Variante orientieren, die man häufiger verwendet: Zugriff auf Ordner oder Zugriff auf Text/CSV-Dateien?

Vor dem Zugriff die entsprechende, geeignete Variante einschalten, beziehungsweise ausschalten?

Irgendwie doof!

„haha – nix schlafen gehen!“ brüllte mein verkorkster Schlafrhythmus und fuhr laut hupend auf einem Bobbicar durch mein Hirn.

Schöne Frage in der letzten PowerQuery-Schulung: wo befindet sich das (auch Excel bekannte) Symbol, das erlaubt nicht nur eine Spalte zu sortieren, sondern nach mehreren:

Die Antwort: ein SYMBOL hierfür gibt es nicht – man muss die Spalten in der gewünschten Sortierreihenfolge anklicken und sortieren, beispielsweise zuerst Ort; innerhalb eines Ortes (Aachen) nach der PLZ, innerhalb einer PLZ (beispielsweise 52062) nach der Straße, …

PowerQuery quittiert die Mehrfachsortierung mit dem Befehl

= Table.Sort(#"Geänderter Typ",{{"Ort", Order.Ascending}, {"Plz", Order.Ascending}, {"Strasse", Order.Ascending}})

Ähnlich wie SQL:

SELECT *
FROM Kunden
ORDER BY Kunden.[Ort], Kunden.[Plz], Kunden.Strasse

Der Teilnehmer war zufrieden.

Herr Ober, können Sie bitte den Daumen von meinem Schnitzel nehmen! – Damit es mir schon wieder runterfällt, oder was?

Hallo Rene,

herzlichen Dank für deine Bemühungen! Das bringt mich ein großes Stück weiter. Leider funktioniert aber irgendeine Kleinigkeit noch nicht… Ich hab viel probiert, komm aber nicht auf den Fehler.

Expression.Error: Der Wert "2022" kann nicht in den Typ "Text" konvertiert werden.

Die Fehlermeldung verstehe ich nicht. Das Jahr, nach welchem gefiltert werden soll.

Hallo Nadine,

ist die Zahl 2023 in der Zelle als Text formatiert?

Und: was macht „geänderter Typ“? – in Text oder Zahl konvertieren?

Liebe Grüße

Rene

Hallo Rene,

genau, ich habe dann extra die 2023 in Text formatiert. Ursprünglich hatte ich es als Zahl, da kam allerdings auch diese Fehlermeldung, weshalb ich die 2023 dann in Text formatiert habe.

Hier die Schritte, welche ich in der Jahrestabelle ausgeführt habe:

Dort wo dann die Formel eingefügt wird, also dort, wo nacher nach diesem Jahr gesucht werden soll, sieht die Formatierung so aus:

Hallo Nadine,

Folgende Ursache: Ich vermute in deiner Zelle stehe die Jahreszahl als ZAHL – in meiner ersten Städtedatei hatte ich sie als Text formatiert.

Damit du auch einen Text erhältst, muss dein zweiter Schritt

= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="SOP"]}[Content],{{"Column1", type text}}){0}[Column1]

verwendet werden

In deinem Code

= Table.SelectRows(Quelle, each (Record.Field(_ , Excel.CurrentWorkbook(){[Name="SOP"]}[Content]{0}[Column1]) <> null))

Zusammengefasst: der in Excel eingetragene Wert in eine Zahl. Die Spaltenüberschrift jedoch ein Text. Irgendwann muss die Zahl in einen Text konvertiert werden!

Wenn euch jemand vor die Frage stellt: Sex oder Magnum – nehmt ihr dann Mandel oder Classic?

Ich habe schon eine Weile überlegen müssen. Folgende Frage erreichte mich:

„Allerdings möchte ich nun in einer Spalte, die nicht fest definiert ist, filtern. Ist dies möglich?

Ziel: Ich möchte das es mir nur die Zeilen anzeigt, die in einer bestimmten Jahres-Spalte einen Wert haben.

Der Anwender des Tools sollte die Möglichkeit haben, ein Jahr einzugeben in einem bestimmten Feld.

Hier wählt der Anwender das Jahr aus. Daraufhin sollte in Power Query in der Spalte, mit der Bezeichnung 2026, nach Werten <> null gefilter werden. Im Screenshot würde dann nur noch die erste Zeile erscheinen, da in der Spalte 2026 nur in der ersten Zeile ein Wert enthalten ist. Ich hab schon viel rumprobiert und bekomm es nicht hin.“

Ich ziehe das Ergebnis des Filters (versehen mit dem Namen „Jahr“) nach PowerQuery und benennen die Abfrage „Jahr“. Sie sieht folgendermaßen aus:

= Excel.CurrentWorkbook(){[Name="Jahr"]}[Content]{0}[Column1]

Ich erstelle eine Spalte mit diesem Wert:

= Table.Column(#"Geänderter Typ", Jahr)

Das Ergebnis:

Wird eine Spalte der Liste gefiltert, lautet der M-Befehl (beispielsweise für das Jahr 1950) folgendermaßen:

= Table.SelectRows(#"Gefilterte Zeilen", each ([1950] <> null))

Baue ich dort allerdings das Ergebnis der Abfrage ein, klappt die Filterung nicht mehr:

= Table.SelectRows(#"Gefilterte Zeilen", each (Jahr <> null))

Auch alle anderen Versuche greifen nicht – werde das Weiterverarbeiten der Tabelle oder Liste noch eine Hilfsspalte, bei der aus

= Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if [1950] <> null then null else "x")

ändert in

= Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if Jahr <> null then null else "x")

Schließlich – der Gedanke: [1950] muss durch

Record.Field(_, Jahr) 

ersetzt werden. Die ganze Zeile lautet laso:

= Table.SelectRows(#"Geänderter Typ", each (Record.Field(_, Jahr) <> null))

oder komplett:

= Table.SelectRows(#"Geänderter Typ", each (Record.Field(_, Excel.CurrentWorkbook(){[Name="Jahr"]}[Content]{0}[Column1]) <> null))

Geht doch!

PS: Die rote Farbe im Kopf habe ich mit der bedingten Formatierung erzeugt. Man darf jetzt nur nicht die Aktualisierung vergessen.

Seit ich nicht mehr rauche, fast keinen Alkohol mehr trinke und mehr Sport treibe, lüge ich häufiger.

Kennst du das? Man möchte in PowerQuery in mehreren Spalten den Datentyp ändern. Ein Klick auf das kleine Symbol und alle Markierungen werden aufgehoben:

Abhilfe schafft der Befehl Transformieren / Datentyp. Lästig:

Die Lösung zeigt Frank Arendt-Theilen:

Man muss die [Strg]-Taste halten und zwei Mal auf das kleine Symbol klicken. Dann klappt es:

Männer können einfach nicht richtig zuhören. – Sind wir gar nicht!

Ich erhalte eine Mail.

„Ich komme leider mit der Fehlermeldung  

     Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.

     Details:

         Key=[Record]

         Table=[Table]

nicht weiter.“

Ich schaue mir das Ganze an. Was haben wir gemacht?

In einem Tabellenblatt werden drei Dateien aufgelistet und der Pfad, in dem sich diese Dateien befinden. Diese vier Zellen haben Namen – hier: Schweinchen1, Schweinchen2, Schweinchen3 und Pfad:

Über Daten / Daten abrufen / aus Datei greife ich auf eine der drei Dateien zu:

Die Datentypen werden nicht automatisch erkannt; übrig bleiben drei Schritte; das Ergebnis wird nach Excel zuzrückgegeben:

Eine der drei Zellen mit Namen wird über Daten / Daten abrufen / Aus Tabelle/Bereich in PowerQuery verwendet. Nach einem Drilldown erhält man den Inhalt der Zelle:

Dies wird für die übrigen Zellen wiederholt. Fügt man nun diese Variablen in den Befehl Excel.Workbook ein, so ist eine Firewall-Meldung die Folge:

Diese kann man umgehen, indem man den Code (Zugriff auf den Inhalt einer Zelle mit Namen) in eine Zeile schreibt:

Excel.CurrentWorkbook(){[Name="Schweinchen3"]}[Content]{0}[Column1]

In der Codezeile

= Excel.Workbook(File.Contents(Pfad & Schweinchen1), null, true)

müssen die beiden Variablen durch ihre Funktion ersetzt werden (was den Code nicht gerade lesbar macht):

= Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Pfad"]}[Content]{0}[Column1] & Excel.CurrentWorkbook(){[Name="Schweinchen1"]}[Content]{0}[Column1]), null, true)

Klappt:

Dies wird auch für die anderen beiden Dateien durchgeführt, die anschließend in Excel geladen werden:

Die Hilfsabfragen Schweinchen1, Schweinchen2, … kann man getrost löschen.

So habe ich die Vorlage erstellt. Und nun kommt die Fehlermeldung:

Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.

Ich begebe mich auf die Suche. Der Fehler taucht beim Zugriff auf das Tabellenblatt „Tabelle1“ auf. Nachgeschaut: bei einer anderen Datei heißt das Tabellenblatt „Sheet1“:

Also muss ich auch noch den „harten“ Namen entfernen. Ich mache es so:

#"Höher gestufte Header" = Table.PromoteHeaders(Quelle{[Item=Quelle{0}[Item],Kind="Sheet"]}[Data], [PromoteAllScalars=true])

und lösche die Zeile „Navigation“. Klappt:

Der ganze Code (jetzt: völlig unverständlich!):

let
    Quelle = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Pfad"]}[Content]{0}[Column1] & Excel.CurrentWorkbook(){[Name="Schweinchen1"]}[Content]{0}[Column1]), null, true),
    #"Höher gestufte Header" = Table.PromoteHeaders(Quelle{[Item=Quelle{0}[Item],Kind="Sheet"]}[Data], [PromoteAllScalars=true])
in
    #"Höher gestufte Header"

Die übrigen zwei Abfragen werden analog angepasst – nun klappt alles!

Hannibal Lecter hat unhöfliche Menschen einfach aufgegessen. Ich wollte es nur einmal gesagt haben.

Eine schöne Frage in der letzten Schulung.

Ein Teilnehmer zeigt mir seine Tabelle. Sie hat sehr viele Spalten, in denen Informationszahlen stehen:

Seine Frage:

„Wenn ich eine Pivottabelle estelle (in der ich die vorkommenden Werte zähle), kann ich nicht die einzelnen Spalten in die Werte ziehen. Was muss ich tun?

Die Aufgabe: Die Orte werden in den Zeilen gruppiert, in die Statusangaben in den Spalten. Man kann nun eine Person in die Zeilen ziehen und sich in den Werten die Anzahl der Einträge anzeigen lassen:

Jedoch: sobald eine zweite Person hinzukommt, arbeitet die Pivottabelle nicht so wie gewünscht:

Da das Ziel war aus einer Pivottabelle ein Diagramm zu erzeugen, scheiden mehrere Pivottabellen aus.

Ich überlege: die Form der Tabelle ist unglücklich gewählt. Man darf die Informationen nicht in Zeilen und Spalten abtragen. Man muss die Tabelle entpivotieren. Hier bietet sich PowerQuery an:

Gesagt, getan – die Liste wird erstellt. Eine Pivottabelle erzeugt:

Genau SO wollte er es haben! Als Basis für ein Diagramm. Er war begeistert.

Wenn eine Schraube locker ist, hat das Leben etwas mehr Spiel

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Die Möglichkeit, den Datentyp über ein Gebietsschema auszuwählen, ist hinlänglich bekannt:

(Randbemerkung: mit der Taste [F] gelangt man am schnellsten zu Englisch / USA)

Benötigt man das andere Gebietsschema mehrmals, kann man dies in den Optionen in den Regionalen Einstellungen der Arbeitsmappe festlegen:

Hinweis: Nicht verwechseln mit den Regionalen Einstellungen, welche die Sprache der Namen der Variablen (beispielsweise Gefilterte Zeilen, Geänderter Typ, Sortierte Zeilen, …) festlegt:

Danke, Martin, für diesen wertvollen Tipp.

Sehr rücksichtsvoll vom Badespiegel, dass er beschlägt, wenn ich aus der Dusche komme.

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Kennt ihr das Problem? Man erstellt eine Reihe von Abfragen:

schließt den Editor, aber aus Versehen lädt man die Abfragen nicht als Verbindung, sondern als Tabelle – padautz – schon hat man 20 (ungewünschte) Tabellen.

Abhilfe schafft in den Optionen die „Standardeinstellung zum Laden von Abfragen“. Wählt man dort die Option „benutzerdefinierte Standardeinstellung“ und deaktiviert alle Kontrollkästchen, so werden die Abfragen nicht als Tabelle in Excel eingetragen.

Danke, Martin, für diesen wertvollen Tipp.

Werdet ihr beim Saubermachen auch immer so abgelenkt von Dingen, die ihr findet?

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Beim Importieren von Daten „erkennt“ PoweryQuery den Datentyp der Spalten. Das kann nervig oder lästig sein oder auch zu Fehlern führen:

Diese Option kann man deaktivieren:

„Spaltentypen und -überschriften für unstrukturierte Tabellen niemals erkennen.“

Danke, Martin, für diesen wertvollen Tipp.

Lass deinen Drucker nie spüren, dass du in letzter Minute ausdrucken möchtest. Sie können deine Angst spüren.

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Wir haben drei Möglichkeiten gefunden, um Werte, die sich in Excel befinden, als Parameter in PowerQuery zu verwenden:

  1. Die Werte stehen in einer intelligenten Tabelle:

2. Die Zellen, in denen sich die Werte befinden, werden mit einem Namen versehen:

3. Die Werte sind das Ergebnis von Berechnungen von Array-Funktionen, beispielsweise FILTER oder SEQUENZ:

Danke, Martin, für diesen wertvollen Tipp.

Gute Mütter lassen ihre Kinder die Rührstäbe abschlecken. Großartige Mütter schalten vorher das Gerät aus.

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Lagert man in Excel einen Datenpfad in eine Zelle, kann man den Wert in PowerQuery verwenden, wenn man einen Drilldown erzeugt hat:

Verwendet man nun diesen Wert, also diese Variable, ist eine Firewall-Meldung die Folge:

Natürlich kann man in den Optionen diese Firewall-Einstellungen ausschalten. Oder man kann das Problem umgehen, indem man den Verweis auf die Excelzelle nicht in einer getrennten Abfrage belässt, sondern in die Formel einbaut, beispielsweise so:

= Folder.Files(Excel.CurrentWorkbook(){[Name="tblPfad"]}[Content]{0}[Pfad])

Danke, Martin, für diesen wertvollen Tipp.

Immer wenn ich ein paar Kilo verliere, finde ich sie kurz darauf im Kühlschrank wieder.

Guten Tag Herr Martin,

Ich habe ein Tabellenblatt, in dem jeden Tag neue Daten eingetragen werden und dann diese wieder gelöscht werden, da diese Daten mit einer Auswertung zusammenhängen. Da am nächsten Tag dort wieder neue Daten eingetragen werden müssen.

Und Power-Query aktualisiert ja im Normalfall nur die aktuellen.

Ich habe nun folgendes versucht wie James Baylay in folgendem Beitrag:

https://blog.jamesbayley.com/2018/04/23/power-query-how-to-load-only-fresh-rows-and-create-an-excel-history-table/

Die Schritte sehen so aus:

let
    Quelle = Excel.CurrentWorkbook(){[Name="tblZusammenfassung"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"ID", Int64.Type}, {"Schulz", Int64.Type}, {"Jackli", type number}, {"Marli", Int64.Type}, {"Jergli", Int64.Type}, {"Michal", Int64.Type}, {"Hans", Int64.Type}, {"Veitli", Int64.Type}}),
    #"Angefügte Abfrage" = Table.Combine({#"Geänderter Typ", tblDieSieben}),
    #"Entfernte Duplikate" = Table.Distinct(#"Angefügte Abfrage")
in
    #"Entfernte Duplikate"

Ich habe folgendes Problem. Wenn ich bei der Abfrage «tblZusammenfassung» Laden-in   / Nur Verbindung erstellen ausführe, dann kommt die untenstehende Fehlermeldung:

Vielleicht haben sie einen Tipp für mich, wie ich das Problem lösen könnte.

Ich würde mich über eine positive Antwort freuen.

Hab heute 20 Cent auf dem Parkplatz gefunden. Ich werde erst mal weiter so leben wie bisher, damit die Nachbarn nichts merken.

Hallo Rene,

mir ist doch noch etwas besseres eingefallen, lässt sich aber auch nicht umsetzen…

Ich wollte jetzt direkt in Power Query filtern nach den Zeilen, welche im Jahr, welches ich im Excel Blatt auswähle, ungleich 0 sind.

Irgendwo passt was noch nicht, hoffe du kannst mir helfen 😀

Hallo Nadine,

Wenn du Leerzeilen / Leerzellen rausfiltern möchtest, dann nicht wie in Excel mit zwei Anführungszeichen, sondern mit Null. Ähnlich wie Datenbanken hat PowerQuery einen eigenen Datentyp für leere Zellen: null:

= Table.SelectRows(#“Geänderter Typ“, each [Datum] <> null)

Und: der Feldname darf nicht in Anführungszeichen gesetzt werden – das Feld heißt: [Datum], nicht [„Datum“].

Ich habe neuerdings die Durchsage „Liebe Kunden, wir öffnen Kasse 2 für Sie“ als Klingelton auf meinem Handy. Einkaufen macht plötzlich viel mehr Spaß!

Hallo Rene,

ich habe schon einiges in Power Query bearbeitet und bin gerade auf dem Stand, dass ich mir durch Filter genau die Daten aus Power Query ziehe welche ich benötige.

Aktuelles Problem: Ich möchte nur die Zeilen haben, welche in dem Jahr Werte haben, welches ich als Filter eingebe. Ich will aber nicht nur das Jahr, sondern alle Werte dann, wenn in diesem speziellen Jahr ein Wert vorhanden ist.

Den Filter „Jahr“ habe ich nicht in Power Query benutzt, da ich noch keine Lösung gefunden habe.

###

Hallo Nadine,

1.) wenn du einen Filter definierst, kannst du ihn auch in PowerQuery „reinziehen“. Danach würde ich ihn als Drilldown in einen Wert umwandeln – etwas so:

2.) Filtere ein beliebiges Datum. Es sieht dann so aus:

= Table.SelectRows(#“Gefilterte Zeilen“, each [Datum] >= #date(2020, 1, 1))

Und nun ersetze ich die Jahreszahl 2020 durch meine „Variable“ aus Schritt 1.

Ich mache im Moment drei Diäten. Von einer alleine wird doch kein Mensch satt!

PowerQuery-Schulung.

Eine Teilnehmerin fragt, wie sie den M-Code einer Abfrage dokumentieren, das heißt in Word speichern kann.

Ich antworte ihr, dass sie den „Erweiterten Editor“ öffnen kann und dort den Code einsehen und herauskopieren kann.

Eine andere Teilnehmerin weist mich darauf hin, wie man alle Codetexte aus allen Abfragen erhält:

„Du musst nach Excel wechseln:

Dort musst du die Abfragen markieren und kopieren:

Und das Ergebnis kann man nach Word einfügen. PowerQuery fügt nicht die Namen der Abfragen, sondern den M-Code ein:

Ich bin begeistert! Ein Dankeschön an Lydia Homann für diesen guten Tipp. Schon nervt Excel wieder ein bisschen weniger!

Kennst du das? Montag Morgen voller Freude aus dem Haus stürmen? – Ich kenne das auch nicht!

Reguläre Ausdrücke – sie sind so klasse – warum mag Microsoft sie nicht?

Die Aufgabe: Extrahieren Sie aus zirka 30.000 Zellen einer Exceltabelle die darin befindlichen Datumsinformationen. Es finden sich Texte wie:

Maistraße 17 (St. 29.03.2016), Flurnummer-alt: 47/11

Bahnhofstraße 1 – 17.3.2022 – Flurnummer-alt: 08/15

Hirtenweg 3A 05-2005 – Garagen, Flurnummer-alt: 00/77

Ich überlege: Formeln wären eine Option. Aber sehr umständlich.

VBA wäre gut.

PowerQuery auch – kennt aber keine regulären Ausdrücke.

Ich entscheide mich für VBA.

Dort muss man einen Verweis auf „Microsoft VBScript Regular Expressions“ einbinden. Oder diese Klasse mit

Set regex = CreateObject("vbscript.regexp")

aufrufen. Dann kann man definieren:

    ' -- Muster: ***01.01.2022***
    strMuster1 = ".*\d{1,2}\.\d{1,2}\.\d{2,4}.*"
    strMuster1_Raus = "\d{1,2}\.\d{1,2}\.\d{2,4}"

Und kann nun extrahieren:

    regex.Pattern = strMuster1
    regexRaus.Pattern = strMuster1_Raus
    regexRaus.Global = True
    For i = 1 To ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
        If regex.Test(Range("I" & i).Value) = True Then
            Set strTreffer = regexRaus.Execute(Range("I" & i).Value)
            j = 0
            For Each strFund In strTreffer
                strTemp = strFund
                If IsDate(strTemp) Then
                    If Len(Split(strTemp, ".")(2)) = 3 Or Len(Split(strTemp, ".")(2)) = 1 Then
                        Range("Q" & i).Offset(0, j).Value = strTemp
                        Range("Q" & i).Offset(0, j).Interior.Color = vbRed
                    
                    Else
                        Range("Q" & i).Offset(0, j).Value = CDate(strTemp)
                        If Year(CDate(strTemp)) > Year(Date) Then
                            Range("Q" & i).Offset(0, j).Interior.Color = vbRed
                        End If
                    End If
                    
                Else
                    Range("Q" & i).Offset(0, j).Value = strTemp
                    Range("Q" & i).Offset(0, j).Interior.Color = vbRed
                End If
                j = j + 1
            Next
        End If
    Next

Vier andere Varianten werden analog abgearbeitet. Klappt.

Warum hat VBA nicht als Standard Regex eingebunden?

Warum kennt PowerQuery keine regulären Ausdrücke?

Warum kann man keine regulären Ausdrücke beim Autofilter oder Spezialfilter eingeben?

Nachtrag: Ich habe etwas gewühlt. Imke Feldmann beschreibt, wie man über JavaScript einen Zugriff auf RegEx erhält:

Trotzdem: ich ziehe hier VBA vor.

Und: vor einigen Jahren hatte ich eine XML-Schulung, in der ich die regulären Ausdrücke vorgestellt hatte. Die Teilnehmerinnen kannten sie, waren damit vertraut, arbeiteten in „anderen Welten“ damit und waren begeistert. Sie wollten sich sogar T-Shirts mit dem Aufdruck „I ♥ RegEx“ drucken lassen. Haben sie aber doch nicht.

Alexa, stell den Wecker auf 4:30 Uhr. – Ich, wenn ich zu Besuch bei jemand bin, der eine Alexa hat.

Hi Rene

Wie geht es dir?

Du, ich muss mich verzweifelt bei dir melden mit einem Excel-Problem. Ich mache einen Import zu WordPress und der Kunde hat mir die Inhalte als Excel geliefert. Es geht um Schadbilder (Gärtner-Themen). Jedes Schadbild wird ein Artikel und sollte deshalb eine Zeile sein. Soweit so gut, jetzt der Kniff: Jeder Text hat Zwischentitel und diese sind aber als Spalten im Excel File angelegt. Also sind die verschiedenen Spalten nicht einzelne Felder in WordPress, sondern ein grosses Textfeld. Und die Spaltentitel sollten jeweils als Zwischentitel in diesen Texten zu finden sein. Die Zwischentitel sollten zudem ein HTML H-Tag erhalten und nicht einfach „fett und grösser“ sein.

Kannst du mir da vielleicht sagen, wie ich weiterkommen kann? Bitte sei ehrlich, wenn das deine Kapazitäten sprengt. Dann machen wir das manuell, das würde auch gehen, es sind um die 140 Artikel.

Ich gestehe – ich habe zuerst überlegt, dieses Problem mit TEXTVERKETTEN zu lösen. Als Trennzeichen hätte ich „</p><p>“ oder Ähnliches eingegeben. Aber irgendwie gefiel mir die Rechnerei nicht.

Warum nicht PowerQuery?

Klar: 1. Schritt: Liste in Tabelle verwandeln. Die Daten aus Tabelle/Bereich importieren:

Das Zauberwort heißt „entpivotieren“. Und schon habe ich eine Tabelle mit zwei Spalten: in der ersten steht die Überschrift, in der zweite die Daten aus den entsprechenden Tabellen:

Und das kann problemlos zu einer Spalte verkettet werden:

"<h1>" & [Attribut] & "</h1>#(lf)<p>" & [Wert] & "<p>"

Die nicht mehr benötigten Spalten werden gelöscht, der Rest in Text konvertiert:

Und zurück nach Excel.

Man hätte die Zeilen in PowerQuery zu einem Wert zusammenfassen können – ich denke, es ist geschickter in Excel mit TEXTVERKETTEN zu erledigen.

Ihre Reaktion:

unglaublich, ich staune! Ich war mir sicher, dass du es kannst, wenn Excel es kann. Aber ich hab schon an Excel gezweifelt.

Danke vielmals!

Wenn man das Müsli mit Eierlikör anrührt, sieht der Tag gleich viel freundlicher aus.

„Guten Morgen,

ich versuche gerade eine PowerQuery-Auswertung aus den Interviewfragebogen zu erstellen.

Ich erhalte allerdings die Fehlermeldung „Die Konvertierung in Number war nicht möglich.

Was mache ich da falsch?“

Was mache ich mit so einer Mail? Richtig: ich schlage vor, mir das Ganze über teams anzusehen. Und tatsächlich:

Okay. Langsam. Von vorne bitte. Können wir uns das Ganze mal bitte in Ruhe ansehen? Was machen Sie?

In einem Ordner befinden sich mehr als 50 Excelmappen:

Jede dieser Mappen hat folgenden Aufbau:

In Spalte A befindet sich in jedem Formular eine Nummer der Form 0., 1., 2., …

Aus einigen dieser Gruppen sollen Informationen ausgelesen werden. Diese Informationen befinden sich in Spalten rechts daneben. Soweit so gut – PowerQuery ist das richtige Werkzeug hierfür. Wir schauen uns das Ganze an – Schritt für Schritt:

  1. Schritt: Leere Arbeitsmappe. Daten / Daten abrufen und transformieren / Daten abrufen / Aus Datei / Aus Ordner

2. Schritt. Der Ordner wird ausgewählt; die Daten werden transformiert.

3. Schritt: Unterordner werden ausgeschlossen; andere Dateitypen ebenso:

4. Schritt: In der Spalte „Content“ befindet sich der Inhalt. Da die Spalten alle den gleichen Aufbau haben, kann man die anderen Spalten löschen und diese Spalte „entpacken“:

Da alle Dateien den gleichen Aufbau und das gleiche Tabellenblatt haben, stellt dies kein Problem dar:

Das Ergebnis:

Da Informationen aus bestimmten „Gruppen“ geholt werden, wird die erste Spalte über Transformieren / Ausfüllen „nach unten gezogen“:

Einige Spalten werden gelöscht. Aus der ersten Spalte werden einige der benötigten Spalten selektiert:

Das Ergebnis wird zurück nach Excel geschrieben (Start / Schließen & Laden / Schießen & Laden in). Obwohl die Daten in Powerquery korrekt angezeigt werden:

ist die Fehlermeldung die Folge:

[DataFormat.Error]. Die Konvertierung in „Number“ war nicht möglich.

Ich stutze. Zurück zu PowerQuery. Vielleicht ist „irgend etwas“ in der ersten Spalte?!? Es sieht nicht so aus:

Aber: „Die Liste kann unvollständig sein.“ Ich lasse mir über Ansicht die „Spaltenqualität“ anzeigen:

Kein Fehler in der ersten Spalte!?!

Wirklich nicht?

Wir wissen, dass PowerQuery zu Beginn nur 1.000 Zeilen auswertet. Bei 50 Formularen x zirka 150 Zeilen sind das 7.500 Zeilen. Okay – ich lasse ALLE Zeilen auswerten, indem ich auf der Statuszeile von 1.000 auf „alle“ wechsle:

Und tatsächlich: JETZT lautet die Beschriftung der Zeile „Spaltenqualität“

Unerwarteter Fehler.

Aha!

Ich gehe auf die Suche – Schritt für Schritt zurück. Schon bald ist klar, dass die Häufigkeit der Fehler unter 1% liegt:

Der Fehler tritt auf, als der Typ geändert wird. Moment – DAS habe ich doch gar nicht gemacht:

Richtig: in Datei / Optionen und Einstellungen / Abfrageoptionen lautet die Grundeinstellung:

Spaltentypen und -überschriften für unstrukturierte Quellen immer erkennen. Und richtig: Das produziert den Fehler:

[DataFormat.Error]

Aha – diese Einstellung bewirkt, dass aus 0., 1., 2., … die Zahlen 1, 2, 3, … werden. Das heißt: in einer der Dateien befindet sich wahrscheinlich in Spalte A eine andere Informationen.

Welche Datei? Zurück zum Anfang:

Ich entferne die erste und die zweite Spalte (den Dateinamen) nicht:

Bevor der Datentyp geändert wird, lasse ich mir alle Inhalte anzeigen:

und stelle fest, dass in einer (oder mehreren) Zellen ein Punkt vorhanden ist:

Da ich die Dateinamen „sehe“, kann ich die Spalte in den Datentyp „Text“ konvertieren und den Übeltäter filtern:

Als Text erzeugt der Punkt kein Problem, allerdings bei der (automatischen) Umwandlung in Zahl.

Die Lösung liegt auf der Hand: entweder man löscht den Punkt in PowerQuery raus oder man geht auf die Suche in der Datei:

Und dann funktioniert die Zusammenfassung problemlos:

Fazit: Vermeiden Sie – wenn möglich – die automatische Datenkonvertierung.

Verwenden Sie ALLE Daten bei der Fehlersuche.

Verwenden Sie die Werkzeuge der Registerkarte Ansicht, also: Spaltenqualität, Spaltenprofil und Spaltenverteilung.

Hört bitte auf, euch an Weihnachten den perfekten Mann zu wünschen! Ich wurde schon drei Mal gekidnappt.

Christian ist irritiert. Ich auch.

PowerQuery stellt für Zahlenformate alle (nur denkbaren) Varianten auf Basis der Gebietsschemata zur Verfügung. Allerdings fehlt die ISO-Norm bei der Kalenderwoche.

Okay.

Wir haben eine Liste mit Ländernamen, die sortiert werden:

Es fällt auf, dass PowerQuery streng nach Groß- und Kleinschreibung sortiert. Deshalb steht die USA vor Ungarn:

Das kann man mit dem Befehl each Text.Upper korrigieren:

Aber: Österreich befindet sich am Ende. Das Alphabet wird US-amerikanisch sortiert. Und: der Befehl Sort stellt keinen Parameter zur Verfügung eine Länderkennung einzutragen. Im Deutschen wird a < ä < b sortiert, im Spanischen a < b < c < ch < d … < l < ll < m < n < ñ < o …

Für jedes Land, das heißt: für jede Sprache müsste man eine Hilfstabelle anlegen. Sehr mühsam!

Danke an Christian Gröblacher für diesen Hinweis.

Advent ist, wenn der Dachboden entrümpelt und alles wieder in der Wohnung verteilt wird.

Boah, was ist denn das? Ich bin sehr irritiert!

Ich öffne im Windows-Explorer das Eigenschaftenfenster einer Datei und wechsle auf die Registerkarte „Sicherheit“. Dort finde ich den Dateinamen mit Pfad, den ich markiere und nach Excel kopiere. Achtung: Ich markiere von rechts nach links:

Ein zweites Mal – jetzt wird von links nach rechts markiert und anschließend kopiert:

Ich kopiere beide Varianten nach Excel – die erste ist oben, die zweite unten. Ich ermittle die Anzahl der Zeichen mit LÄNGE und bin erstaunt. Ich löse das erste Zeichen mit der Funktion LINKS heraus und bin wieder erstaunt:

Wandelt man das Zeichen vor dem Laufwerksbuchstaben D mit Code in den ASCII-Code um und mit ZEICHEN wieder zurück, so erhält man ein „?“

Ich bin erstaunt.

Noch schlimmer wird es, wenn man mit PowerQuery und diese Access-Datenbank zugreift

und den Pfad durch den ersten Text ersetzt:

DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.

lautet die Fehlermeldung. Man sieht das Zeichen nicht – weder in Excel noch in Word, im Editor oder in PowerQuery. Und wundert sich über diese merkwürdige Fehlermeldung.

Da gibt es nur eine Lösung: Will man den Dateinamen mit Pfad ermitteln, muss man im Explorer über das Symbol „Pfad kopieren“ den Verzeichnisnamen und Dateinamen in die Zwischenablage kopieren.
(danke an Martin Weiß für diesen Tipp)

Wenn ich zwei Dinge gleichzeitig kann, dann ist es lächeln und dabei Mordgedanken hegen.

Nennen wir ihn B. B. kann für Björn stehen. Oder für Benno. Für Benjamin oder für Boris. Egal. Wir nennen ihn B.

B. ist Teilnehmer meiner Excelschulung und stellt eine Frage zum Aufbereiten von CSV-Dateien, die er in regelmäßigen Abständen erhält. Er denkt an eine VBA-Lösung – ich schlage PowerQuery vor. Die Datei wird aufgerufen, transformiert und nach Excel zurück geschrieben.

Allerdings: der Pfad, beziehungsweise der Dateiname soll variabel sein. Eigentlich kein Problem, denke ich, und lasse B. Pfad und Dateiname in die Excelmappe schreiben, mit einer Überschrift versehen und in eine (intelligente) Tabelle umwandeln.

Beide Tabellen werden nach PowerQuery gezogen, und dort mit einem Drilldown in einen Text verwandelt. Sie werden in dem Befehl

File.Contents

verwendet; die Sicherheitsstufe dieser Arbeitsmappe wurde ignoriert. Und dann das Erstaunliche:

DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.

Stirnrunzeln.

Probieren. Beispielsweise Pfad und Dateiname in PowerQuery (oder in Excel) zu verketten und diese Zeichenkette zu verwenden. Beides schlägt fehl:

Immer wieder die gleiche Fehlermeldung:

DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.

In Ruhe, alleine, und ohne B. schaue ich mir die Zeichenkette genau an und probiere. Erstaunt stelle ich fest, dass das erste Zeichen nicht der Laufwerksbuchstabe ist. In Excel kann man das mit der Funktion LINKS oder TEIL ermitteln. Der ASCII-Code lautet 63 – eigentlich ein Fragezeichen.

Ich überlege, probiere und frage B. Er hat eigentlich nur den Namen des Verzeichnisses aus den Dateieigenschaften kopiert. Und ich habe ihm zugesehen.

Ich weiß nicht, wie dieses merkwürdige Zeichen in die Excelzelle gelangt ist. Ich weiß, dass Excel bei einigen Zeichen (geschützte Leerzeichen, bedingte Trennstriche, …), die man über Word, Outlook oder eine Webseite nach Excel kopieren kann, Probleme hat. Aber hier? Keine Ahnung.

Lösung des Problems: Pfad neu tippen – und dann klappt es!?!

Nehmen Sie Ihren Köter hier weg, ich spür schon einen Floh! – Komm, Rex, gehn wir. Die Frau hat Flöhe.

Das ist mir ja noch gar nicht aufgefallen. Volker hat darauf aufmerksam gemacht:

In einer Excelmappe befindet sich ein Tabellenblatt („Kontinente“) mit sechs intelligenten Tabellen: tbl_Europa, tbl_Antarktis, tbl_Afrika, …

Die Datei wird einmal als Excel-Arbeitsmappe (XLSX) und einmal Excel-Binärarbeitsmappe (XLSB) gespeichert.

Greift man mit PowerQuery auf die XLSX-Mappe zu, ist das Ergebnis bekannt: angezeigt wird das Tabellenblatt und die sechs intelligenten Tabellen:

Beim Zugriff auf die XLSB-Datei dauert der Zugriff nicht nur sehr, sehr lange – angezeigt wird nur das Tabellenblatt:

Danke an Volker Pagel für diesen Hinweis.

Sein Fazit: Don’t use xlsb!

Seine Kollegen kommentieren es:

Andreas:

Vielleicht ist xlsb dem alten Format xls zu ähnlich. Die xls aus 2003 lässt sich auch nicht einlesen.

Jens:
Volle Zustimmung!

Über Excel.Workbook ([Content]) werden xlsb auch nicht erkannt.

Ist echt nen Problem…xlsb ist bei uns relativ beliebt, da xlsm nicht per Mail versendet werden kann. Makro sind BÖSE 

Aber xlsb kann auch Makros enthalten.

Oberste Regel beim Putzen mit lauter Musik: die Klobürste ist nie, nie, nieeee das Mikrophon. Niemals!

Schöne Frage in der letzten PowerQuery-Schulung: warum kann man eigentlich keine Duplikate ermitteln lassen? Oder – wie in Excel – Duplikate löschen lassen?

Stimmt – DAFÜR gibt es in PowerQuery leider keinen Assistenten. Muss man „per Hand“ machen.

Tanja Kuhn schreibt: „Das geht beides. Duplikate löschen per Rechtsklick. Duplikate anzeigen über Gruppierung.“

Danke für den Hinweis – zur Gruppierung hätte der Teilnehme, der sich so eine Option beim Import der Daten gewünscht hatte, sicherlich angemerkt, dass man es dann auch „Duplikatensuche“ nennen sollte. Das „Duplikate löschen“ habe ich glatt übersehen / vergessen … (ich schäme mich! *lach*)

Der Teilnehmer dachte übrigens beim Verknüpfen von zwei Tabellen in einer 1:n-Beziehung an Access, bei dem beim Aktivieren der referentiellen Integrität automatisch überprüft wird, ob alle n-Elemente auf der 1-Seite vorkommen. So einen Haken oder eine Meldung hat er vermisst.

Ich gehe mal raus. Habe gehört, bei dem Sturm fliegt alle 11 Minuten ein Sigle vorbei. Ich orkanshippe jetzt.

Einfach übersehen. PowerQuery-Schulung. Wir wollen auf einen Sharepoint-Ordner zugreifen:

Geht aber nicht. Ich frage meinen Freund und Kollegen Hans-Peter Pfister um Rat. Seine Antwort:

Hoi René

Nur kurz, ohne viel drum-herum, bin gerade unter Wasser.

Nimm den SharePoint Ordner Konnektoren, nicht den für SP-Liste.

Das Leben kann manchmal so einfach sein!

Heute kommt Mutti und bringt zehn Original Thüringer mit. Ich weiß gar nicht, wo die alle schlafen sollen.

PowerQuery-Schulung. Ein Teilnehmer sagt, dass er nicht den gesamte Ordnernamen sehen kann und deshalb nicht den richtigen Ordner deselektieren kann.

„Dann schieben Sie halt die Bildlaufleiste nach rechts“, meine ich. „Geht nicht!“

Was ist pasiert?

Wir üben in der PowerQuery-Schulung den Zugriff auf Ordner:

Der Teilnehmer hat die Dateien (auf OneDrive) in einem sehr, sehr langen Ordnernamen abgelegt. Und wirklich: es ist dann leider nicht mehr möglich, die Bildlaufleiste so zu verschieben, dass ich das rechte Ende des Ordners sehen kann:

Die Lösung: Da ich den Text kenne, der am Ende steht, kann ich den gewünschten Ordner auch über „endet nicht mit“ filtern. DAS klappt.

Superman und Chuck Norris hatten eine Wette. Der Wetteinsatz: der Verlierer muss in Strumpfhosen rum laufen. Der Ausgang ist bekannt.

Gestern PowerQuery-Schulung. Wir üben und probieren den Zugriff: Excelmappen, Textdateien, XML, json, die SQL-Datenbank, Ordner, Web, … alles klappt.

SharePoint?

Der Teilnehmer kopiert seinen Sharepoint-Pfad in das Eingabefeld:

Und noch bevor ich sagen kann, dass er sich über das Microsoft-Konto – drei Zeilen darunter – anmelden muss, erhält er eine Fehlermeldung:

Zweiter Versuch: erneute Anmeldung. Das Resultat: sofortige Fehlermeldung ohne die Möglichkeit sich über das „Microsoft-Konto“ anzumelden. Wie gelangt man wieder dort hinein?

Es dauert eine Weile, bis wir es gefunden haben:

Man muss über die Datenquelleneinstellungen den Pfad löschen:

… dann wird man bei der nächsten Anmeldung wieder nach ALLEN Einstellungsoptionen gefragt.

Immer wenn ich Berichte über Haiattacken sehe, frage ich mich ,wie blöde muss man denn eigentlich sein? Ich meine – das hört man doch schon an der Musik, wenn der Hai näher kommt.

Schöne Frage gestern in der PowerQuery-Schulung:

Warum kann man bei vielen Befehlen, beispielsweise dem Filtern, den Dialog wieder anzeigen lassen, um dort schnell Änderungen vorzunehmen:

Jedoch nicht beim Ändern des Datentyps?

Man müsste die Korrekturen in der M-Codezeile vornehmen oder erneut in den entsprechenden Spalten.

Eine der Nudeln schwimmt im Kochtopf oben. Das macht mich wahnsinnig: ist die tot oder was?

Hallo Rene

Es ist unglaublich, aber ich habe wirklich das Gefühl, dass ich in jeden «Sche…sstopf» falle, welchen Microsoft zu bieten hat.

Seit 2 Tagen kämpfe ich mit dem Problem, dass in einer table in jeder Zelle scheinbar versteckte Tabs vorhanden sind.
Dies hat natürlich die traurige Konsequenz, dass damit s- oder wverweise auf diese table kläglich scheitern und zu #NV Fehlern führen.

Zum Problem mit Tabs hast Du ja den Artikel tabulatoren | Excel nervt … (excel-nervt.de) geschrieben, doch in meinem Fall hilft mir dieser (wenigstens im Moment) nicht wirklich weiter.

Ich muss dazu vielleicht etwas ausholen und den Vorgang beschreiben, welcher mich zum Problem geführt hat.
Am Anfang steht Excel File mit einer table. Diese table wird mittels Power Automate in eine SharePoint Online Liste geschrieben.

In einem anderen Excel File werden die Daten der SharePoint Liste wieder mit einer PowerQuery Abfrage eingelesen und stehen somit wieder in einer table, auf welche ich eben mit dem erwähnten wverweis zugreifen möchte. Der Befehl führt eben zu dem #NV und nach langem Suchen, habe ich letztendlich herausgefunden, dass in der abgefragten table in allen Zellen ein tab steht.
Interessanterweise ist es aber so, dass in der table sämtliche Zellwerte linksbündig angezeigt werden. Klicke ich dann bei denjenigen Zellen welche eine Zahl enthalten nicht auf sondern in die Zelle, dann springen die Zahlen nach rechts (ohne dass ich ausser dem Klick in die Zelle etwas anderes mache)
Noch verwirrender (wenigstens für mich) ist die Tatsache, dass die Zellformatierungen danach erhalten bleiben. Ich meine damit, dass diejenigen Zellen in welche ich wie beschrieben einmal reingeklickt habe, auch nach einem reload der Power Query Abfrage erhalten bleiben.

Hast Du vielleicht eine Erklärung für dieses Verhalten?
Wieso und wann wurden die Tabs in die Zellen geschrieben und gibt keine Möglichkeit dies zu beeinflussen?

Bezugnehmend auf Deine vorherige Antwort ist es aber sicherlich schon so, dass man solche Phänomene auch mit der besten Schulung nicht abwenden kann ☹

Würde mich auf jeden Fall darüber freuen, wenn Du eine Idee zu meinem neuen Problem hättest

Lieber Gruss

Hallo Herby,

das Problem ist mir und vielen anderen bekannt – ich würde es nicht als Anomalie, sondern als Bug von Excel bezeichnen.

Wirf mal einen Blick in das PDF in

https://www.compurem.de/buecherdownloads/Zahlenformate.zip

– dort beschreibe ich mehrere Lösungen (mein Liebling ist Daten / Text in Spalten) und auch, wie dieses Phänomen zustande kommt.

Liebe Grüße

Rene

Hallo Rene

Danke für die abermals hilfreiche Unterstützung

Mein Problem schein aber irgendwie anders gelagert zu sein und entgegen meiner vorherigen Problemschilderung ist es leider nicht so, dass der Fehler mit einem Klick in eine der betroffenen Zellen «nachaltig» gelöst wird.
Zur besseren Veranschaulichung habe ich eine Kopie der Tabelle erstell, welche auf der PQ Abfrage beruht. Am Bsp der Zelle B2 kannst Du sehen, dass der Zellwert nach einem Klick in die Zelle, nach rechts gesprungen ist.
Sobald ich das bei irgend einer benötigten Zelle mache, welche einen Zahlenwert enthält, springen die Werte nach rechts und die Formeln mit den darauf referenzierenden Zellen, funktionieren.
Wenn ich hingegen die PQ Abfrage aktualisiere, springen die Zahlen wieder nach links und die Formeln bringen den #NV

D.h die PQ Abfrage erzeugt die falschen Daten und dabei spielt es überhaupt keine Rolle, wie die Zellen formatiert sind.

Die Spalten der Daten Quelle (ShareListe) sind ausnahmslos als standard formatiert und dies lässt sich auch nicht ändern, da innerhalb einer Spalte unterschiedliche Daten vorhanden sind.
Wie bei Excel gibt es beim PQ unter Transformieren/Bereinigen die Trim Funktion, mit welcher eigentlich ein tab aus einer Zelle entfernt werden sollte.
Aber bis dato ist mir dies damit nicht gelungen

Das Problem muss beim erzeugen der Tabelle gelöst werden, da die Daten dynamisch sind und laufend aktualisiert werden. Oder anders ausgedrückt, eine neue Abfrage würde die vormals vorgenommenen Korrekturen mir den Daten überschreiben.

Das File Servicekatalog Quelldaten dient als Datenquelle, das heisst wenn sich irgendwelche Daten vom Servicekatalog geändert haben, werden diese dort eingepflegt. Eine Flow schreibt die Daten in die SharePoint Liste, welche dann wie PQ Abfrage von überall in eine Servicekatalog.xlsx gelesen werden können. Die Quelldatei hat das Problem auf jeden Fall nicht, d.h entweder auf dem SharePoint oder bei anschliessenden PQ Abfrage wird ein problematischer tab angehängt ☹

Vielleicht mache ich einen Denkfehler und/oder Du hast eine Idee, was ich ändern muss

Lieber Gruss

Hallo Herby,

Das Problem ist Folgendes:

In einer Spalten stehen Zahlen und Texte.

Wird diese Liste nach PowerQuery „gezogen“ und dort der Typ nicht explizit angepasst, so bleiben die Zahlen Zahlen (rechtsbündig) und die Texte Texte.

Verwendet man in PowerQuery jedoch den Datentyp „Text“, dann „schiebt“ Excel unter diese Zahlen ein Textformat (das so nicht sichtbar ist).

Da die Zelle als Standard (oder Zahl) formatiert ist, verschwindet das Textformat beim Editieren (Doppelklick) der Zelle. Andererseits: Nach Aktualisierung von PowerQuery haben wir die gleiche Situation wie am Anfang.

Gegenfrage: Warum MÜSSEN in einer Spalte Zahlen und Texte stehen? Das widerspricht einem Datenbankdenken.

Und: wenn schon Zahlen – dann sollten sie auch Texte bleiben – als Informationen und nicht zum Rechnen verwendet werden.

Liebe Grüße

Rene



Solange mir niemand das Wasser reichen kann, nehme ich das Bier.

Excelstammtisch. Hartmut zeigt, dass man das Datenmodell von Excel nach PowerBI importieren kann.

Ich frage, ob er wisse, wann das zu Problemen führt. Und zeige eine Datei:

Darin befinden sich Tabellen, die ins Datenmodell geladen wurden. Mit Hilfe des Datenmodells wurde eine Pivottabelle erstellt. Die Tabellen wurden mit Measures angereichert und sind untereinander verknüpft.

Nun will ich diese Datei (genauer: die Daten, Verknüpfungen und Measures) nach PowerBI importieren:

Ich erhalte eine Fehlermeldung – fast nichts wird importiert:

Wir machen uns auf die Suche – Hartmut wird fündig. Man darf nicht die Daten in Tabellen in der Arbeitsmappe halten und diese ins Datenmodell laden, sondern man muss sie mit PowerQuery importieren. So:

Diese Daten werden nun ins Datenmodell geladen – dort kann man sie verknüpfen

und mit Measures anreichern:

Das Ergebnis:

Ein erneuter Import nach PowerBI Desktop:

Klappt!

Ein Dankeschön an Hartmut Hilbich für das Suchen und Auffinden der Lösung des Importproblems. Hartmut schreibt dazu:

„Das Problem bestand hier (besteht!) darin, dass PBID das Datenmodell selbst sehr wohl importiert, aber nicht gleichzeitig auch die Quelltabellen!

Ich habe die Quelltabelle mit PQ abgefragt und das PP-Modell exemplarisch mit 2 Measures versehen. Der Import in PBID funktioniert dann einwandfrei!

Mein Fazit: Es ist nicht ratsam, die Quelldaten physikalisch gemeinsam mit dem PP-Modell zu speichern. Also entweder die Daten direkt mit PP abfragen, oder aber (besser) mit PQ abfragen. Was also innerhalb von PP kein Problem ist, wird dann aber eines beim Import in PBID.

Good to know!“

An alle, die behaupten, sie wüssten, wie der Hase läuft: Er hoppelt!

PowerBI-Schulung. Wir greifen auf Excelmappen zu, die auf Sharepoint liegen. Es kommt die Frage, ob man auch auf einen Sharepoint-Ordner zugreifen kann. Klar kann man:

Man muss nur den Ordnerpfad kopieren und eintragen:

Und – erhält einen Fehler:

Ach, klar, natürlich: man muss sich natürlich noch anmelden. Ist ein bisschen versteckt:

Erstaunlicherweise kann man JETZT OHNE Anmedlung in Excel über PowerQuery auf einen Sharepoint-Ordner zugreifen. Muss ich das verstehen?

Mal den Teufel nicht an die Wand! – Aber das ist doch ein Spiegel, in den du hineinschaust …

Amüsant: ich habe eine große Excelliste mit mehrere Tausend Datensätzen. Ich bearbeite sie in PowerQuery:

Ich importiere eine zweite Liste und verknüpfe sie mit einem Left outer Join:

Das Ergebnis sieht in PowerQuery gut aus:

Ich lade die Tabelle zurück nach Excel und erhalte einen Fehler:

Zurück zu PowerQuery versuche ich einen Right outer Join:

Die Ursache? PowerQuery zeigt nur 1.000 Datensätze. Wenn in der Liste DANACH eine Zelle mit einem fehlerhaften Wert steht, wird er bei einem Left Outer Join nicht angezeigt. Erst in Excel. Natürlich kann man sich in PowerQuery auf die Suche nach dem fehlerhaften Datensatz machen und ihn entfernen. Oder in Excel:

Danke an Christa für diesen Hinweis und danke für die Bemerkung, dass die Fehlermeldung in älteren PowerQuery-Versionen eine andere war:

Ich habe jetzt genau das richtige Alter. Muss nur noch herausfinden für was.

Nicht aufgepasst. Nicht genau hingeschaut.

Gestern habe ich für den Excelstammtisch einige Dateien vorbereitet, um zu zeigen, was PowerQuery verlangsamt. Ich habe eine Liste mit Dummy-Namen mit 20.000 Datensätzen:

Diese verknüpfe ich mit einer Liste, die zwei Zeilen lang ist:

Das Ergebnis: 28.877 Datensätze

Ein zweiter Blick auf die Liste zeigt, dass einige Zeilen (nicht alle!) nun zwei Mal in der Liste auftauchen:

Nein – an der Verwendung eines Primärschlüssels liegt es nicht – die Zeile

= Table.AddKey(#"Geänderter Typ",{"Geschlecht"}, true)

ist nicht der Grund!

Schließlich finde ich des Rätsels Lösung: ich habe beim Verknüpfen der beiden Listen aus Versehen die Fuzzy-Logik aktiviert.

Haken raus – und ich erhalte wieder die ursprünglichen 20.000 Zeilen.

Ich habe gerade versucht, den Mähroboter mit einem Grasbüschel vom Nachbarn an den Gartenzaun zu locken. War wohl schon satt.

Zuerst habe ich mich geärgert. In PowerQuery gab es früher ein Symbol „Von Tabelle“. Daraus wurde in der Gruppe „Daten abrufen und transformieren“ das Symbol „Aus Tabelle/Bereich“.

Seit ein paar Tagen heißt es nun „Vom Blatt“

Muss das sein? Ständiges Umbenennen?

Frank Arentd-Theilen hat mich auf den Grund hingewiesen (danke für den Hinweis):

Ja – denn nun kann man Listen in Excel, die mit den neuen Arrayfunktionen erstellt wurden, beispielsweise mit FILTER, SORTIEREN und SORTIERENNACH in PowerQuery importieren:

Das funktioniert auch mit der Funktion SEQUENZ:

Okay – zugegeben – leider nicht immer. Wenn diese Matrixfunktionen innerhalb einer Liste stehen, wie beispielsweise hier in diesem Monatskalender:

dann wandelt PowerQuery die gesamte Liste in eine (intelligente) Tabelle um und – scheitert! Klar: Tabellen dürfen keine Matrixfunktionen verwenden …

Als Kind dachte ich, dass Spargel und Erdberen illegal seien, weil wir ihn aus Autos auf abgelegenen Parkplätzen gekauft haben.

Hallo Rene,

Ich suche jemanden, der fit in power query und power pivot ist und dem ich ca. eine stunde lang fragen stellen kann.

Hintergrund: Ich habe einen größeren Auftrag, da geht es um mehrere Tools im Excel-Umfeld, da geht es bei einem Tool jetzt erst mal darum, ob power query da was bringen würde.

Ich hatte Schlimmes oder Schwieriges befürchtet. Aber die zentrale Frage war weder schlimm noch schwierig zu beantworten.

Gegeben sei eine Auftragstabelle mit Verkaufsdaten:

Diese Liste wird nach PowerQuery gezogen und dort bearbeitet. Das Ergebnis wird zurückgegeben:

Die erste Frage lautete: Wie kann man Anfang und Ende als Filter in PowerQuery einbauen?

Die Antwort:

  • Man muss die jeweils zwei Zellen in eine intelligente Tabelle konvertieren.
  • Man muss diese ebenso nach PowerQuery importieren.,
  • Dort den Datentyp in Datum ändern.
  • Und dort ein Drilldown durchführen. Das heißt: die Tabelle in einen Wert, besser: in eine Variable, verwandeln.
  • Diese Variable hat einen Namen – er kann verwendet werden.

Also so:

Man schaltet einen beliebigen Datumsfilter ein („Zwischen“):

Und ersetzt in M die beiden Werte durch die Variablennamen:

Fertig! Test in Excel:

Und natürlich kam danach die Frage:

ich möchte die berechnung lieber in PowerPivot vornehmen und mit einer Pivottabelle gruppieren und das Meassure verwenden.

Ich habe jetzt in DAX diesen ausdruck, der funktioniert:

MeineSumme:=
CALCULATE(SUM(tblBestellungen_pre[Frachtkosten]);
DATESBETWEEN(tblBestellungen_pre[Bestelldatum];
date(2019;2;1); date(2019;2;27)) )

Weißt du, wie ich hier jetzt das Datum aus den Zellen hineinbekomme?

Auch DAS war schnell geklärt:

Hi Claus,

wenn tblFrom und tblTo im Datenmodell liegen, dann mit:

MeineSumme:=CALCULATE( SUM(tblBestellungen_pre[Frachtkosten]);DATESBETWEEN(tblBestellungen_pre[Bestelldatum]; MAX(tblFrom[From]); MAX(tblTo[To])))

Und kann man Datenmodell und Meassure in einer Pivottabelle in Excel verwenden.

Hübsche Fingerübung!

Natürlich spreche ich mit mir selbst. Manchmal brauche ich eben eine kompetente Beratung.

Gestern im Excelstammtisch. Frank Arendt-Theilen macht darauf aufmerksam, das PowerQuery einen Parameter bei der Funktionen RUNDEN (Number.Round) anbietet:

IntelliSense zeigt allerdings in M, dass diese Funktion einen weiteren Parameter besitzt: roundingMode mit fünf Konstanten:

Die Standardeinstellung von PowerQuery ist RoundingMode.ToEven. Damit unterscheidet sich diese Rundenfunktion von RUNDEN in Excel. Dort wird RoundingMode.AwayFromZero verwendet. Deshalb unterscheiden sich diese beiden Runden-Funktionen: PQ rundet wie VBA; Excel rundet anders …

Vielen Dank, Frank für diesen sehr, sehr wertvollen Hinweis!

Und: während sich auf der Microsoft-Hilfeseite

https://docs.microsoft.com/de-de/powerquery-m/number-round

leider kein Hinweis dazu findet, hat Ken Pulse es sehr gut erläutert:

https://www.excelguru.ca/blog/2014/09/17/power-query-the-round-function/

XLarium weist darauf hin, dass es noch weitere Artikel und Videos zu diesem Thema gibt:

MrExcel hat vor kurzem ein Video dazu gemacht:

Weitere Videos zum Thema Runden:

MROUND In Power Query

TRUNC In Power Quer

CEILING oder FLOOR in Power Query

Nach zwei Stunden Babysitten glaube ich, dass ich doch keine Kinder, sondern lieber etwas Harmloseres möchte. Krokodile zum Beispiel. Oder Löwen.

Hallo Excel-Meister

ich arbeite an einem VBA – Projekt, dass aus Power BI Dateien die Metadaten rauslesen soll.

Das Auslesen geschieht über Power Query (what else….), aber ich muss noch ein paar Prüfungen mit VBA erstellen und insbesondere die Power Query Abfragen on the fly erstellen. Letzteres geht problemlos.

Der Ablauf:

  1. Prüfe, ob User die pbix geöffnet hat.
  2. Falls nicht, bitte freundlich darauf aufmerksam machen
  3. Falls nein, Abbruch – falls ja, pbix öffnen.

Bis dahin klappt alles.

Nun kommt der Punkt, wo der Benutzer sich gegenüber der Power BI Datei authentifizieren muss, nachdem er ja gesagt hat.

falls er aber den Dialog hier abbricht (…..DAU…….), kommt eine „schöne“ Meldung:

Nun meine Frage:

Wie kann ich hier meine eigene Meldung einbauen und vor allem, wie fange ich das ab?

Bin schon voller Zweifel…..

Merci, lieber René für deine Geduld mit mir

Freundliche Grüsse Hans Peter

########################################

die unwissenden erleuchten sich selber

habs gefunden. nach Drücken von „Senden“ fiel es mir wieder ein, da stand was im Buch von René

ich danke dir!

Hier der Code, falls es dich interessiert.

Sub GetData()

‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‚ Code erstellt durch: Pfister BI Consulting GmbH

‚ Zweck: Holt Metadaten aus der Power BI Datei

‚ Erstelldatum: 8.3.2021

‚ Aenderungsdatum:

‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‚Testen, ob pbix Datei geöffnet ist. Falls nicht, Mesagebox und fragen, ob sie geöffnet werden soll. Fall nicht, Abbruch

    If Dateigeoeffnet(Range(„Dateipfad_PBIX_Original“)) = False Then

        If MsgBox(„Die Datei muss geöffnet sein. “ & Chr(10) & “ Soll die Datei geöffnet werden?“, vbYesNo, „Power BI Datei öffnen?“) = vbNo Then

            Exit Sub

            Else: Call Open_PBIX

                Application.Wait (Now + TimeValue(„0:00:10“))

            End If

   End If

’notwendige Abfragen aktualisieren

Abfragen_starten:

On Error GoTo ErrHandler

    ActiveWorkbook.Connections(„Abfrage – Tabellen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Memory Usage Tabellen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Tabellenliste“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Liste nicht geladene Queries“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Abfragen – nicht geladen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

  Call Listen_befuellen

ErrHandler:

    ‚Fehler No. 1004 abfangen

    If Err = 1004 Then

        If MsgBox(„Soll der Prozess abgebrochen werden?“, vbYesNo, „Bitte Identifikation vornehmen“) = vbYes Then

            Exit Sub

        ’sonst Abfrage wieder aufnehmen

            Else: Resume Abfragen_starten

        End If

    End If

End Sub

Gruss Hp

Nachricht von den Nerven: „Falls du uns suchst – wir sind am Ende.“

Erstaunlich! Auf dem letzten Excelstammtisch, den Frank Arendt-Theilen organisiert hat, hat er angemerkt, dass die PowerQuery-Funktion Date.WeekOfYear, die man über Spalte hinzufügen / Datum / Woche / Woche des Jahres nach dem US-amerikanischen Modell rechnet. Zwar verfügt Excel seit vielen Versionen über die Funktion ISOKALENDERWOCHE, welche die KW korrekt nach ISO 8601 berechnet. Auch Outlook unterscheidet bei den Kalenderwochen zwischen USA und Europa. Jedoch nicht PowerQuery. „Haben die das vergessen?“, fragt Frank. Also muss man diese Funktion in PQ nachbauen …

Was sagt der Teig zum Bäcker? – Ich bin gerührt!

Guten Tag Herr Martin,

ich bin durch Zufall auf Ihren Blog aufmerksam geworden.

Leider hat Excel immer wieder merkwürdige Verhaltensweisen.

Warum auch immer.

Ich habe auf einem Rechner ein Problem mit der Funktion Daten „Abrufen und transformieren“

Ich kann auf dem PC keinerlei Daten über diese Funktion aufrufen.

Keine CSV noch eine Tabelle aus einem SQL Server.

Ich bekomme immer wieder die Fehlermeldung, dass die Initialisierung der Daten fehlgeschlagen sei.

Lade ich die CSV auf einem anderen Rechner ein, funktioniert dies Problemlos.

Gleiches gilt bei der SQL Abfrage.

Haben Sie noch eine Idee wo ich das Problem suchen kann?

In der PowerQuery kann ich die Daten sauber sehen.

Nur das Einfügen der Daten aus der Verbindung in das Tabellenblatt funktioniert nicht.

Vielen Dank für Ihre Zeit. Mit besten Grüßen,

####

Ich habe es mir gerade angesehen: Excelversion 2016.

Ich erstelle eine intelligente Tabelle, ziehe sie in PQ, klappt; ich bearbeite, ich tue, ich mache, M-Code ist vorhanden – klappt.

Ich lade sie als Verbindung. Klappt.

Aber sobald ich die Daten als Tabelle „sehen“ / „haben“ möchte – knallt es:

Kennt jemand diesen Fehler?

Initialisierung der Datenquelle schlug fehl.
Überprüfen Sie den Datenbankserver oder kontaktieren Sie Ihren Datenbankadministrator. Vergewissern Sie sich, dass die externe Datenbank verfügbar ist, und wiederholen Sie den Vorgang. Sofern diese Nachricht wieder erscheint, erstellen Sie eine neue Datenbankquelle um mit der Datenbank Verbindung aufzunehmen.

Sweet dreams are made of cheese, who am I to dis a Brie?

In der letzten Excelschulung waren wir erstaunt. Wir verknüpfen mehrere Tabellen miteinander:

Warum dauert das Verknüpfen der Daten in PowerQuery so lange?

Die Ursache war schnell gefunden: die Teilnehmerin hatte den Cursor nicht in die Liste gesetzt und so aus der Liste eine (intelligente) Tabelle erzeugt, sondern die ganzen Spalten markiert und dann diese (mit den leeren Zeilen) in eine Tabelle umgewandelt.

Der Anfang der Tabelle:

Und das Ende:

Als wir den Fehler entdeckt hatten, wollte die Teilnehmerin den Bereich „per Hand“ nach oben ziehen:

Ich werde nervös, wenn Aktion SOOO lange dauern. Ein kurzer Blick … das muss doch schneller gehen … und wirklich: es geht schneller. Das Werkzeug „Tabellengröße ändern“ in der Registerkarte „Tabellenentwurf“ bietet eine schnelle Möglichkeit Tabellen zu vergrößern und verkleinern. Man muss nur $A$1:$E$2156 tippen – und schon ist die Tabelle kleiner. Und PowerQuery schneller!

Ich nehme ja gerne mal Anhalter mit. So fangen zwar viele Horrorfilme an, aber auch viele Pornos.

Mourad Louha stellt die neuen Datentypen vor, die man über Powerquery erstellen kann:

http://www.excel-ticker.de/eigene-datentypen-mit-power-query-erstellen/

In der Registerkarte „Transformieren“ findet man das Symbol, um eigene Datentypen zu erstellen:

Gibt man die Daten zurück, erhält man das typische Symbol der Datentypen:

Mit einer Datenüberprüfung wird eine Dropdownliste geschaffen. Aus ihr wird das Attribut ausgewählt: =[Zelle].Attribut:

Mourad bemängelt, dass man die Datentypen leider nicht in Abfragen verwenden kann. Schade ist auch, dass sie nicht in der Registerkarte erscheinen:

Einige Dinge fehlen – aber ich zuversichtlich, dass Microsoft hier „nachrüsten“ wird.

Hast du schon wieder mein Deo benutzt? – Ich bin Robin Hood! Ich stehle und verteile es unter den Armen!

Ups, das ist mir noch nie aufgefallen! Warum? Weil ich SO nicht arbeite.

Aus einer Datenquelle wird mit PowerQuery eine Abfrage gestartet. Auf diese Liste wird eine Formel aufgesetzt, allerdings nicht in der Schreibweise

=KKLEINSTE(Artikel[Einzelpreis];Artikel[@Einzelpreis])

rechnet, sondern in der Bezugsschreibweise:

=KKLEINSTE($F$2:$F$78;ZEILE(A1))

Die Originaldatenquelle ändert sich – sie wird kleiner. Die Verbindung wird aktualisiert:

Die Folge: Die Formel wird angepasst, beispielsweise in:

=KKLEINSTE($F$2:$F$15;ZEILE(A1))

Fehlermeldungen sind die Folge.

Ändert sich die Liste erneut und wird nun länger, werde diese Bezugsfehler natürlich nicht korrigiert …

Fehler in der Berechnung sind die Folge.

Heißt: Wenn schon (intelligente/formatierte/dynamische) Tabellen – dann bitte die Bezüge auf diese Tabellen in Tabellenschreibweise und nicht in Bezugsschreibweise! Sonst gibt es Ärger!

Pflanze im Internet bestellt. Email erhalten: „Ihre Bestellung ist eingegangen.“ Neue bestellt.

Wer mit Datenbanken arbeitet, der würde in dieser Liste sofort eine n:m-Beziehung erkennen: einer Rebsorte sind mehrere Länder zugeordnet. In einem Land werden es mehrere Rebsorten angebaut.
(zugegeben: das Original-Beispiel, das mir die Teilnehmerin zeigte waren Firmendaten aus dem Bereich firmeninterne Weiterbildungen – aber die Struktur war die gleiche)

Auch wenn die Länder durch einen Umbruch in eine Zelle eingefügt wurden, kann man sie mit dem Assistenten „Text in Spalten“ oder mit PowerQuery trennen.

Allerdings sollen nun Zuordnungen zu den Ländern getroffen werden. Das heißt: in einer Zeile steht ein Land oder mehrere Länder NEBENEINANDER.

Zum Glück stellt PowerQuery den Assistenten entpivotieren zur Verfügung:

Damit werden nicht nur die Zeilen n Mal wiederholt, sondern die zugehörigen Länder stehen auch UNTEREINANDER, was zur Weiterverarbeitung (SVERWEIS oder was auch immer) hervorragend geeignet ist:

Tipps gegen Schimmel im Badezimmer: Langsam mit einer Karotte ins Bad gehen. Dann zum nächsten Pferdehof locken.

Sind mehrere Texte in einer Zelle untereinander geschrieben, kann man sie mit dem Assistenten Daten / Text in Spalten trennen.

Und die Blitzvorschau? Ein Versuch ist es wert:

Sieht gut aus – jedoch:

Okay zu lang. Na ja! Und der zweite Wert? Versagt, weil in der zweiten Zeile nur ein Wert steht. Die Anzahl der Werte, die sich untereinander befinden, ist unterschiedlich groß:

Auf ein Neues: PowerQuery. Seit einigen Versionen stellt PowerQuery – anders als der Assistent „Text in Spalten“ als Trennzeichen den Zeilenumbruch zur Verfügung:

Splitter.SplitTextByDelimiter(„#(lf)“

Klappt! Klappt hervorragend!

Können Sie M? Da wir wissen, dass der Zeilenumbruch in Excel dem Wert 10 entspricht hätte man auch verwenden können:

Character.FromNumber(10)

Also:

„Split Column by Delimiter“ = Table.SplitColumn(Source, „Anbauländer“, Splitter.SplitTextByDelimiter(Character.FromNumber(10), QuoteStyle.Csv), …

Klappt auch:

Gib einem Mann einen Putzlappen und er wird ihn ratlos angucken. Gib ihm einen Hochdruckreiniger und er wird alle reinigen, soweit das Kabel reicht.

Einmal links, einmal rechts – da muss ich jedesmal hinschauen. Kennt ihr das? Ich habe eine Datei erstellt oder geöffnet und modifiziert und möchte die Datei oder die Applikation schließen OHNE zu speichern. Sei es, weil ich etwas ausprobieren wollte, weil ich nur drucken wollte, weil die Änderungen falsch waren, weil Excel volatile Funktionen neu berechnet hat… Es gibt eine Reihe von Gründen. Also: schließen/beenden OHNE ZU SPEICHERN. Wohin klicken? „Nicht speichern“ – rechts:

Eben: „Nicht speichern“ – rechts:

Ups: noch ein Button mehr:

Hier auch:

Oder ich muss genau hinschauen, um zu verstehen, dass „NEIN“ „nicht speichern“ bedeutet:

In PowerQuery muss ich wieder rechts klicken – allerdings: „Verwerfen“:

Und hier: richtig: links klicken! Damit das Hirn wieder funktioniert! Die Aufmerksamkeit auf den Text gelenkt wird! Bloß keine Langeweile oder Gewohnheit aufkommt:

Kühlschrank sauber gemacht. Der Käse hielt ein Referat über Vergänglichkeit und hatte die Haare schön.

Hallo Herr Martin,

herzlichen Dank für Ihre Mühe!.

Unabhängig davon möchte ich Ihnen nochmals für die sehr gelungene Onlineschulung am Freitag danken.

Sehr praxisnah und super erklärt! Ich freue ich schon auf PowerBI im September.

Bei dem File gibt es noch ein kleines Problem

Formula.Firewall: Abfrage ‚Tabelle 1‘ verweist auf andere Abfragen oder Schritte und kann daher nicht direkt auf eine Datenquelle zugreifen. Erstellen Sie diese Datenkombination neu.

Ich kann mir dies im Moment nicht im Detail ansehen (Pfad scheint korrekt, Files vorhanden), werde aber am Nachmittag nach Anleitung versuchen, dies „nachzubauen“.

Könnte ein Kompatibilitätsproblem sein (Excel-Version). Bekomme vor öffnen von Power Query diese Meldung:

Kompatibilitätswarnung: Die Abfragen in dieser Arbeitsmappe sind u.U. nicht mit Ihrer aktuellen Version von Excel kompatibel.

Hallo Herr Martin,

Ihre Liste funktioniert doch – es waren meine Sicherheitseinstellungen.

Nochmals vielen lieben Dank!

Brettspiel für eine Person? – Bügeln!

PowerQuery-Schulung. Frage eines Teilnehmers:

Von einer Liste sollen gleiche Werte gruppiert und deren Elemente durch Komma getrennt in einer Zelle zusammengefasst werden. Also, aus

A 1
B 2
B 3
C 4

Soll werden:

A 1
B 2, 3
C 4

Ich habe eine Weile überlegen müssen. Und hier die Lösung:

Schritt 1: Die Tabelle wird importiert. Die erste Spalte wird sortiert, damit die Elemente untereinander stehen. Diese Tabelle wird dupliziert:

Die zweite Tabelle wird gruppiert, so dass die Anzahl der Elemente ermittelt werden kann:

Diese beiden Tabellen werden mit einander verknüpft (Home / Kombinieren / Abfragen zusammenführen / Abfrage als neue Abfrage zusammenführen):

Die Tabelle wird „aufgeklappt“.

Eine Indexspalte, die mit 0 beginnt, wird eingefügt.

Die Funktion List.Range gruppiert die Werte:

List.Range(#“Added Index“[Artikelname],[Index],[#“Artikel (2).Anzahl“])

Zur Erklärung:

  • #“Added Index“[Artikelname] ist die Spalte aus der die Daten gruppiert werden.
  • [Index] – ab dieser Position wird gruppiert
  • ],[#“Artikel (2).Anzahl“] – so viele Elemente werden gruppiert

Steht ein Wert beispielsweise drei Mal in der Liste, können die letzten zwei Werte gelöscht werden. Dies erreicht man mit dem Befehl „Duplikate entfernen“, den Sie in Home / Zeilen verringern / Zeilen entfernen finden.

Das Ergebnis sieht dann wir folgt aus:

Knifflige Aufgabe …

Ich habe einige Punkte von der ToDo-Liste auf die WasSolls-Liste gesetzt.

Amüsant. Während Inquire auch ausgeblendete Blätter anzeigt (sowohl hidden als auch very hidden)

zeigt PowerQuery nut die sichtbaren Blätter an:

Nein! Nicht ganz. Wenn man die Daten nun transformiert und zurück zur Source wechselt, so sieht man auch dort alle Blätter – egal ob sichtbar oder ausgeblendet:

Danke an Dominique Dauphin für diesen wertvollen Hinweis!

Der Ernst des Lebens beginnt in dem Moment, wo du keine Gratikwurstscheibe mehr bekommst.

Ist das im Sinne des Erfinders?

Ich habe eine Tabelle. Ich lege einen Druckbereich fest. Ich möchte, dass Menschen zwar die Tabel drucken dürfen, aber die Daten nicht stehlen, also nicht einfach rauskopieren.

Also wird ein Schutz auf das Tabellenblatt gelegt.

Wir wissen, dass PowerQuery von Dateien Tabellenblattnamen, Namen und intelligente Tabellen anzeigt. Da der Druckbereich als Name bespeichert wird, wird er angezeigt.

Kann geladen werden und nach Excel zurückgeschrieben werden.

Und schon hat man Zugriff auf die Faten. Ohne die Datei zu entzippen, das Protect-Element zu löschen, …

Fische sind – statistisch gesehen – die an seltensten überfahrenen Tiere.

Hallo Herr Dauphin,

das ist sehr spannend, was da passiert. Ich habe die Ursache gefunden:

Die Überschrift Ihrer Tabelle ist länger als 255 Zeichen.

Sie speichern die Arbeismappe. Sie greifen mit PowerQuery auf diese Datei zu. PQ greift mit der Zeile

= Table.TransformColumnTypes(#“Höher gestufte Header“,{{„ID“, Int64.Type}, {„Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.“, type number}})

darauf zu. Wir schreiben die Tabelle als intelligente Tabelle zurück nach Excel. Tabellen dürfen maximal 255 Zeichen in der Überschrift haben. Excel schneidet die restlichen Zeichen ab.

Soweit klappt de Workflow. Wenn ich allerdings diese Datei schließe und öffne, erhalte ich die Fehlermeldung.

Die Ursache: die Überschrift endet jetzt nicht mehr auf „dolor sit amet“.

Eine weitere Fehlermeldung ist die Folge:

Sehr spannend.
Lösung: Mit einer intelligenten Tabelle arbeiten! Dann ist die maximale Anzahl der Buchstaben der Überschrift begrenzt.

Danke für den Hinweis.
Schöne Grüße
Rene Martin

Hömma. Wer zum Geier is eigentlich dieser Lars Krismes?

Heute in der PowerQuery-Schulung kam die Frage, ob man ab einer bestimmten Spalten alle anderen Spalten bis zum Ende der Tabelle löschen könne:

Schöne Frage. Geht aber nicht mit den Hausmittel. Ein paar Zeilen Code M wären nötig.

Ich habe vorgeschlagen von der ersten bis zu der Spalte mit der [Umschalttaste] zu markieren und anschließend „Andere Spalten entfernen“. Ist ein Klick mehr. Geht aber auch …

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: