Eine Liebeserklärung an MS Excel
Auf einem Tabellenblatt befindet sich eine intelligente Tabelle. Sie heißt „Tabelle1“.
Sie wird in das Datenmodell eingefügt. Die Tabelle in Power Pivot heißt „Tabelle1“:
Die Ursprungstabelle wird umbenannt.
Leider wird die Tabelle in PowerPivot NICHT automatisch umbenannt:
Sehr schade, findet das ein Teilnehmer der letzten Power Pivot-Schulung.
Ach wie doof! Die Funktion OR (ebenso wie AND) kann in Power Pivot – anders als in Excel – nur zwei Argumente aufnehmen! Also genau so wie die Funktion CONCATENATE
Ob man die Funktionen in Power Pivot auf Deutsch anzeigen lassen könne, fragte eine Teilnehmerin.
Leider nein!
Ein Teilnehmer in der Schulung erklärt mir, dass er Formeln nicht mein einem Gleichheitszeichen, sondern mit einem Pluszeichen beginne:
Dafür benötige man nur einen Finger. Excel schreibt das Gleichheitszeichen davor:
Warum das nicht in Power Pivot gehe, will er wissen:
Ich schmunzle amüsiert.
Eine schöne Frage in der letzten PowerQuery-Schulung.
Eine Tabelle soll verändert werden. Im linken Bereich befinden sich Informationen (nennen wir sie „Metadaten“), im rechten Bereich in mehreren Spalten weitere Informationen zu diesen Metadaten.
Jede dieser Gruppen, bestehend aus jeweils drei Spalten, soll neben die anderen Daten geschrieben werden, so dass die Metainformation so oft auftaucht, wie Gruppen vorhanden sind. Dabei können beliebig vieler dieser Gruppen auftauchen.
Das Ziel:
Mein erster Gedanke:
Ich fasse mit dem Befehl „Spalten zusammenführen“ jeweils die einzelnen Spalten einer Gruppe zusammen:
Anschließend kann man diese Spalten entpivotieren
und danach am Trennzeichen (hier: „|“) teilen.
Aber: das Verfahren ist umständlich, weil (hier:) bei 17 Gruppen 17 Mal entpivotiert werden muss. Da die Anzahl der Gruppen variabel ist, ging ich auf die Suche, ob man das mit geschickten M-Befehlen (einer Schleife!) abkürzen und dynamisch halten kann.
Da fiel mit der Artikel von Hildegard Hügemann in die Finger:
https://www.office-kompetenz.de/inhalte-aus-spalten-auf-zeilen-verteilen-mit-power-query/
Und genau DAS ist die Lösung:
Zuerst muss man den „rechten“ Teil entpivotieren:
Anschließend werden die Überschriften benötigt in der Form A – B – C. Leider stehen sie hier als A1 – B1 – C1 – A2 – B2 – C2 – A3 – … Die Zahlen müssen entfernt werden. Man kann sie mit dem Assistenten „Spalte teilen“ und er Option „Nach Wechsel von Nicht-Ziffer zu Ziffer“ herauslösen:
DIESE (spätere Überschriftsspalte) wird nun pivotiert, wobei die Werte (letzte Spalte) natürlich nicht aggregiert werden (verbirgt sich in den „Erweiterten Optionen“):
Der Rest ist „Kosmetik“: Datentypen festlegen, Spalten löschen, leere Spalten entfernen (wegfiltern), Spalten umbenennen, …
Klasse!
Ein großes Dankeschön an Hildegard Hügemann für die Lösung – hier habe ich glatt „in die falsche Richtung gedacht“.
Excelstammtisch letzte Woche.
Frank zeigt die unterschiedlichen Orte, an denen man Measures erstellen kann.
Wählt man Power Pivot / Measures / Measures verwalten, so kann man den Dialog an allen vier Seiten (und der Ecke) vergrößern:
Dagegen in einer Pivottabelle, deren Daten dem Datenmodell hinzugefügt wurden, kann der Dialog „Measure bearbeiten“ nur an der Ecke vergrößert werden:
Amüsant.
Danke an Frank Arendt-Theilen für den Hinweis.
Hallo Rene,
Ich habe für meine Kollegen zur Budgetplanung 2022 je Abteilung ein Excel auf Teams eingestellt.
Das Excel enthält eine Power Query Abfrage auf alle Abteilungs-„Auftragsbücher“, und in PowerPivot ein Datenmodell für die Beziehungen zwischen den Tabellen.
MAC Benutzer scheinen aber Probleme mit der Datei zu haben (s. Screenshots unten)
Wie kann man die volle Funktionalität der Datei auch für MAC Benutzer herstellen?
Es wäre super, wenn Du hier einen Rat hast
Vielen Dank und beste Grüße Katrin
Hi Katrin,
1. Antwort: Mac ist nicht meine Welt – ich habe keinen.
2. Antwort: ich weiß, dass der mac lange Zeit nicht PowerQuery unterstützt hat; soweit ich weiß, kann er das inzwischen.
3. Antwort: der Mac unterstützt (noch) nicht das Datenmodell von Excel.
4. Schau mal:
Liebe Grüße
Rene
PS: Danke an Hans-Peter Pfister für den Link.
Eine schöne Frage in der letzten PowerBI-Schulung:
Wie viele Funktionen kann man in DAX ineinander verschachteln. „Genug“ lautete meine Antwort. „Sehr viele“, um etwas präziser zu sein. Ich habe gesucht und nicht gefunden. Erstaunlich. Also habe ich ausprobiert. Aber 100 Ebenen habe ich aufgehört:
Das dürfte genügen.
Fürs Erste.
Schöne Frage in der letzten PowerBI-Schulung, auf die ich keine Antwort wusste:
Warum steht bei den Beziehungen in PowerBI eine Kreuzfilterrichtung zur Verfügung:
im Datenmodell in Excel dagegen nicht?
Übrigens: Gute Erklärungen, was Kreizfilterrichtungen sind, finde ich auf:
und auf:
https://docs.microsoft.com/de-de/power-bi/transform-model/desktop-create-and-manage-relationships
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!“
Letzte Woche in der PowerBI-Schulung. Wir schauen uns einige der DAX-Funktionen an. Beispielsweise CONCATENATE.
Es folgt die Frage, warum man mit der Excel-Funktion VERKETTEN ( CONCATENATE) mehrere Texte verketten kann:
Dagegen in DAX:
Zwei Texte sind kein Problem. Jedoch bei drei Texten:
=CONCATENATE(tbl_ottos_mops[ottos];" ";tbl_ottos_mops[mops])
Also verketten wir in DAX mehrere Texte mit dem et-Zeichen „&“. Das funktioniert!
=tbl_ottos_mops[ottos]&" "&tbl_ottos_mops[mops]&" "&tbl_ottos_mops[Spalte1]&" "&tbl_ottos_mops[Spalte2]
Diese Seite zu den DAX-Funktionen sollte Microsoft noch einmal überarbeiten.
https://docs.microsoft.com/de-de/dax/dax-overview
Zum einen finden sich dort verwirrende Denglish-Erklärungen. Zum andere erstaunen Aufzählungen. Die trigonometrische Kreisfunktion TAN wird den statistischen Funktionen zugeordnet. Ups!
Seltsam – da fehlen einige Spalten in PowerPivot.
Aber des Rätseln Lösung ist schnell gefunden: es gibt eine Schaltfläche, mit der man ausgeblendete Spalten und Tabellen anzeigen lassen kann oder „echt“ ausblenden lassen kann:
Übrigens: nicht nur in der Diagrammansicht, sondern auch in der Datenansicht!
Seltsam. Ich erstelle eine Pivottabelle, bei der ich mehrere Tabellen miteinander im Datenmodell verknüpft habe. Schon der erste Blick zeigt, dass das nicht korrekt sein kann:
Ein Blick in die Diagrammansicht von PowerPivot gibt Antwort: eine Verbindung wurde auf inaktiv gesetzt:
Man sieht es auch in der Feldliste der Pivottabelle:
Verbindung wird aktiv setzen und –
schon sieht das Ergebnis besser aus!
Nicht konsequent.
Will man in einer Tabelle in PowerPivot mehrere Spalten ausblenden, muss man sie einzeln markieren., wenn sie nicht nebeneinander liegen. Man kann sie dort nicht mit gedrückter [Strg]-Taste markieren.
Wechselt man jedoch von der Datensicht in die Diagrammsicht, ist es ohne Weiteres möglich mit gedrückter [Strg]-Taste mehrere Spalten zu selektieren.
Masterclass bei den Exceldays 2019 in Sofia bei Gasper Kamensek (http://excelunplugged.com/) zum Thema „Power Pivot“. Er zeigt uns, das nach dem Erstellen einer KPI in PowerPivot zuerstet nur Zahlen zu sehen sind:
Desaktiviert man allerdings den Status und aktiviert ihn anschließend wieder von Neuem, werden die Farben korrekt angezeigt:
Böse!
Zwei Pivottabellen – eine rechnet richtig, eine nicht. Gerechnet werden soll nicht:
(10+20)x(10+20) = 900
sondern
10×10 + 20×20 = 500
Wenn Sie genau hinschauen, sehen Sie den Unterschied:
In der ersten Pivottabelle wurde mit einem Berechneten Feld gearbeitet, das nicht so rechnet, wie ich es gerne hätte.
Im unteren Beispiel wurde die (formatierte/intelligente) Tabelle ins PowerPivot-Datenmodell gezogen – dort wurde gerechnet und das Ergebnis in Excel in einer Pivottabelle ausgegeben.
Amüsant.
Ich greife mit Power Query (Daten abrufen und transformieren) auf eine Tabelle einer Internetseite zu aus der ich die Wechselkurse heraushole. Ich sortiere die Liste und stutze:
Warum steht die USA vor Ungarn?
Klar: Power Query ist case sensitiv und unterscheidet beim Sortieren zwischen Groß- und Kleinschreibung. Also: Türkei < USA < Ungarn.
Eine Lösung: Man ersetzt „USA“ durch „Usa“, sortiert und ersetzt anschließend „Usa“ in „USA“. Oder man ändert in M (im erweiterten Editor) den Sortierbefehl:
#“Sortierte Zeilen“ = Table.Sort(#“Umbenannte Spalten“,{{each Text.Upper([Land]), Order.Ascending}})
Dabei steht „Umbenannte Spalten“ für den vorhergehenden Befehl und „Land“ für die zu sortierende Spalte.
Die Funktion Text.Upper ändert die Schreibweise der Länderbezeichnungen in Großbuchstaben und sortiert diese.
Übrigens: Anders als Excel gibt es in Power Pivot leider keinen Schalter (oder Parameter), um die Sortierung nicht case sensitive durchzuführen.
Manchmal bin ich zu schnell beim Nörgeln. Manchmal übersehe ich Dinge. Wie beispielsweise in folgendem Beitrag:
Schade. In der letzten Power Query-Schulung kam folgende Frage: Während PowerPivot sehr schön die Beziehungen grafisch darstellen kann:
gibt es eine solche Ansicht in Power Query (Daten abrufen und transformieren) leider nicht:
Ich weiß, dass die Sprache M anders denkt als PowerPivot, aber dafür, dass Power Query so viele Joins zur Verfügung stellt … Schade!
Danke für den Hinweis an XLarium, dass man über die Registerkarte „Ansicht“ die Abfrageabhängigkeiten darstellen lassen kann:
Nur noch =DATUM(2018;10;12)-HEUTE() also 33 Tage bis zu unseren ExcellentDays in München.
Neugierig? Hier findest du weitere Informationen:
http://www.munich-office-group.de/
Inquire ist ein mächtiges Tool, das eine komprimierte Datenanalyse einer Arbeitsmappe liefert, für deren Elemente man lange suchen müsste. Ich gestehe – ich habe es nicht gleich verstanden:
Die einzelnen gefundenen Elemente werden auf der linken Seite aufgelistet und sind dort einsehbar. Warum jedoch liefert der Export in eine andere Datei keine Informationen, sondern schreibt einfach nichts in die leere Datei?
Wohin verschwinden die Informationen?
Die Antwort: Ich hätte auf der linken Seite die Objekte auswählen müssen, die ich exportieren möchte. An vielen Stellen warnt Excel – hier jedoch nicht und überlässt dem erstaunt auf die leere Arbeitsmappe Schauenden die Suche. Ein Hinweis, dass nichts ausgewählt wurde wäre einfach schön gewesen … Vielleicht in Excel 2019.
Mit PowerPivot können Felder aus verschiedenen Tabellen des Datenmodells verknüpft werden. Excel verlangt dabei, dass mindestens eine Tabelle nur eindeutige Werte besitzt (die 1-Seite der 1:n-Beziehung). Falls dies nicht der Fall ist, wird es mit einer Fehlermeldung quittiert.
Bedauerlicherweise werden nicht die Datentypen überprüft. So ist es möglich, dass man Text mit Zahl oder Datum verknüpfen kann. Diese Funktion hätte man auch integrieren können …
Geht nicht!
Text mit Zahl – das geht schon !?!
Wer sich mit PowerPivot beschäftigt, der stellt fest, dass zusätzlich zu Excel ein weiteres Programm geöffnet wird. Das bedeutet: Wenn die Eingabe in Excel nicht beendet wird, erhalten Sie beim Öffnen von PowerPivot eine lustige Fehlermeldung.
Umgekehrt – wenn Sie beispielsweise in PowerPivot eine Formel eingeben oder ändern; die Eingabe jedoch nicht beenden, erhalten Sie „auf der anderen Seite“ (in PowerPivot) auch eine Fehlermeldung:
Das Perfide: Man „sieht“ die andere Seite nicht – man muss explizit hinüber wechseln.
Ich habe es mal versucht: Mit Powerquery kann man seit Excel 2010 Daten aus dem Internet holen. Ich habe wikipedia angezapft – die Liste der größten Städte Deutschlands:
https://de.wikipedia.org/wiki/Liste_der_Gro%C3%9F-_und_Mittelst%C3%A4dte_in_Deutschland
Mit PowerQuery werden Daten aus dem Internet geholt.
Daraus wird nun mit PowerView (PowerMap) eine Karte erstellt. Der erste Blick auf die Karte zeigt, dass Berlin in den USA liegt:
Wo ist Berlin?
Auch beim Näherzoomen finden sich einige Orte außerhalb Deutschlands:
Büren, Herne, Burgdorf, Puchheim, …
Und wo ist Herne?
Schade eigentlich, dass man das Seitenlayout des Tabellenblatts, das mit PowerView erstellt wurde, nicht anpassen kann:
In Excel kann man eine einzelne Formel in einer Zelle ohne abschließende Klammer schreiben, also:
=SUMME(A1:A17
=SVERWEIS(C5;A1:B27;2;FALSCH
=MITTELWERT(C2:C200
und so weiter
Leider allerdings nicht bei den DAX-Funktionen bei PowerPivot:
Hier muss die schließende Klammer geschrieben werden.
Microsoft kann einfach nicht einheitlich:
Erstellt man aus einer Excelliste eine Tabelle (eine intelligente Tabelle; eine formatierte Tabelle) über Einfügen / Tabelle (um daraus anschließend mit PowerPivot zu arbeiten), schlägt Excel vor, dass die Liste eine Überschrift hat.
Wählt man den direkten Weg, erstellt also eine PowerPivot-Tabelle aus einer Liste (PowerPivot / Zu Datenmodell hinzufügen), fragt Excel nicht, ob die Liste eine Überschrift enthält.
Hätte man ja auch einheitlich machen können …
Was mache ich nun schon wieder falsch? Ich habe eine Tabelle. Ich wandle sie in eine intelligente Tabelle (formatierte Tabelle) um. Gebe ihr einen Namen (tbl_Kunden). Füge sie über PowerPivot zum Datenmodell hinzu.
Klappt alles prima. Nun möchte ich allerdings ein berechnetes Feld im Meassure-Bereich einfügen:
Summe_Jahresbeitrag_Platinum:=CALCULATE(SUM([Jahresbeitrag 2016]);[Mitgliedschaft]=“Platinum“)
Ein Fehler ist die Folge:
Die Antwort: PowerPivot verlangt an einigen Stellen den Tabellennamen – auch wenn Sie nur eine Tabelle verwenden. Nicht bei SUM, sondern beim zweiten Parameter der Funktion CALULATE:
Summe_Jahresbeitrag_Platinum:=CALCULATE(SUM([Jahresbeitrag 2016]);tbl_Kunden[Mitgliedschaft]=“Platinum“)
Damit klappt es.
In Excel 2010 musste man die PowerPivots hinzuladen. In Excel 2016 sind sie in Excel integriert. Und Excel 2010? Nun – man muss sie aktivieren: Datei / Optionen / Add-Ins / Verwalten – und ein Klick auf PowerPivot? Aber wo sind sie? Auch „Hinzufügen“ hilft nicht weiter – ich finde sie nicht.
Wo ist PowerPivot?
Die Antwort: Das PowerPivot-Add-In ist kein Excel-Add-In, sondern ein COM-Add-In. Wird diese Option ausgewählt, kann man sie leicht hinzufügen:
Und schon erscheinen PowerPivot, Inquire und Power View.