Category Archives: Power Query

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: