SYNOPTIC DESIGNER FOR POWER BI – CUSTOM VISUALS

Mit dem Synoptic Designer für Power BI können individuelle Grafiken erstellt und integriert werden. Hierfür nutzen ich den Power BI Designer (Desktop Version)  für Windows.
[Download: https://powerbi.microsoft.com/de-de/desktop/]

Aufbau

In diesem Artikel möchte ich demonstrieren wie ein Fußballfeld in einen Bericht integriert wird, um Statistiken aus einem Fußball-Spiel visuell besser darstellen zu können.

Das Spielfeld ist schnell selbst gezeichnet oder aus dem Internet geladen und auf der Synoptic Webseite [https://synoptic.design/ ] wird dies eingefügt und die Bereiche werden markiert:1

Daten

Die korrespondierende Excel Tabelle mit den Daten ist folgendermaßen aufgebaut:

2

Die Spalte „Area“ gibt auch den Namen im Designer, dadurch erfolgt die Zuordnung der Daten auf das Spielfeld, bzw. die Grafik.

Integration

Im Designer exportiert man nun die Grafik mit den Markierungen via „Export to Power BI“ als SVG Datei.

Zuerst wird aber die Excel Tabelle in den Power BI Designer importiert:

3

Anschließend erstellen wir noch folgende Measures zum zählen des Spielstandes:

  1. Tore Team B = CALCULATE(COUNTA(Spieldaten[Aktion]); Spieldaten[Aktion]=“Tor“; Spieldaten[Team] =“B“)
  2. Tore Team A = CALCULATE(COUNTA(Spieldaten[Aktion]); Spieldaten[Aktion]=“Tor“; Spieldaten[Team] =“A“)
  3. Tore Gesamt = [Tore Team A]+[Tore Team B]

 

Der nächste Schritt ist, die sogenannten Custom Visuals in den Designer zu verankern, dafür wählt man auf der Webseite „Get the latest Version“ und lädt die Datei herunter. Anschließend wird diese integriert:

4

Nun kann dieses Panel in den Bericht integriert werden.

Design und Aufbereitung

Nachdem das Panel positioniert wurde, werden die Felder aus den Daten eingesetzt:

Area -> Legend

Aktion -> Values

5

 

 

 

 

 

 

 

 

Anschließend wird via „select map“ die heruntergeladene Karte (SVG Datei) ausgewählt.

Nun werden noch weitere Grafiken positioniert und befüllt:

6

 

 

 

 

 

Okay, anschließend noch das Dashboard auf das kostenlose Power BI Konto hochladen und im Web veröffentlichen:
Link

PowerPivot: Switch() Funktion – oder auch: Wie komme ich ohne Hilfstabellen bei Übersetzungen aus?

PowerPivot_LogoEnglish version below

Wenn man einfache Übersetzungen in Daten vornehmen wollte, bspw. Gruppenschlüssel in Namen zu übersetzen, bediente ich mich bisher immer an Hilfstabellen die ich in Excel definiert und dann in das Datenmodell eingebunden haben.

So in etwa – eine Tabelle beinhaltet einen Schlüssel um eine Kundengruppe zu beschreiben. Die Beschreibung befindet sich in einer Hilfstabelle die in PowerPivot eingebunden wird und per Verknüpfung dann die Übersetzung vornimmt. Problem dabei ist meist, wenn ein Wert in den Daten vorkommt, der nicht in der Übersetzung Tabelle vorkommt. Da habe ich dann per einer berechneten Spalte per Bedingter Anweisung die fehlenden Werte markiert.

Hier der vorherige Weg:

2014-08-18_12-37-08 2014-08-18_12-39-03

 

 

  

  

 

  

 

 

 

 

 

Hier kommt nun die SWITCH Funktion ins Spiel.

Mit dieser kann ich Werte überprüfen und übersetzen. Grundsätzlich geht das Ganze auch mit einer IF Funktion, aber deutlich unübersichtlicher und somit schlecht in der Wartung.

=SWITCH(Customers[Group]; "A"; "High Priority"; "B"; "B Customers"; 
"C"; "C Customers"; "no valid group")

Der Grundsyntax von SWITCH:

SWITCH(expression, 
    value1, result1,
    value2, result2,
     :
     :
     else
    )

2014-08-18_12-41-07

 

 

PowerPivot Switch Function – or the end of the linked translation tables?

If you wanted to make simple translations in data, for example I want to translate a Customer Group Key in a name, I used to create translations Tables in Excel and then integrated into the data model of PowerPivot.
Something like that – a table contains a key to a customer group to describe. The description is another table that is linked in PowerPivot and then performs the translation by a relationship. Problem is mostly, if a value exists in the data that is not present in the translation table. Since then I have checked the missing values ​​by a calculated column using a IF/ELSE statement.
Here the previous path:
2014-08-18_12-37-08 2014-08-18_12-39-03

 

 

  

  

 

  

 

 

 

 

 

Here are the SWITCH function:
With this I can check values ​​and translate. Basically, the whole thing goes well with an IF function, but much less clear and thus poorly in maintenance.

 =SWITCH (Customers[Group], "A", "High Priority", "B", "B Customers", 
"C", "C Customers", "no valid group")

The basic syntax of SWITCH:

SWITCH(expression, 
    value1, result1,
    value2, result2,
     :
     :
     else
    )

2014-08-18_12-41-07
 

Einführung in PowerQuery Teil 2 von ?

thIn diesem Eintrag möchte ich das Tutorial in der Einführung in PowerQuery weiterführen. Hier geht es um die Gruppierungsfunktion und das Verbinden zweier Tabellen. Hier werden wir eine Excel Datei auslesen mit 2 Tabellenblättern – eins hält die Bestellungen für das Jahr 2014 und das zweite Blatt die Informationen zu den Kunden. Ziel ist die Auswertung der Umsätze pro Kunde und pro Monat. Normalerweise würde ich das in PowerPivot lösen, aber für eine einfache Aufbereitung der Daten und deren Darstellung in diesem Kontext bietet PowerQuery eine etwas leichtere Methode – da hier gleich die Daten zum Schluss gruppiert zur Verfügung stehen und lediglich dargestellt werden müssen. Vielleicht aber auch Geschmackssache …

pq_1Als erstes wechseln wir auf das Ribbon PowerQuery und laden Daten aus einer Datei, bzw. gleich Excel.

pq_2Im nächsten Schritt befindet sich im Navigator die jeweilige Tabellen aus dem Excel Sheet. Einerseits sehen wir hier die Tabellenblätter aber auch die Tabellen die sich auf den Blättern befinden. Da sich hier jeweils eine Tabelle auf jedem Blatt befindet, wähle ich die Tabellenblätter aus. Hier setze ich gleich die Haken bei beiden, wähle aus, dass die Daten in das Datenmodell (PowerPivot) geladen werden.

Weiterhin wähle ich die erste Tabelle Bestellungen aus und klicke auf Bearbeiten.

 

 

 

 

 

pq_3

Nun muss in beiden Tabellen jeweils die erste Zeile noch als Tabellenüberschrift gekennzeichnet werden. Nachdem dies bei den Bestellungen durchgeführt wurde, klicke ich auf Anwenden und schließen und wähle die Tabelle Kunden im Navigator aus und Bearbeite diese genauso:

pq_3_4Nachdem die Kundentabelle ebenfalls die Überschriften aus der ersten Zeile entnimmt, schließe ich ebenfalls das Fenster über Anwenden und schließen und klicke im Navigator auf Laden damit beide Excel Tabellen in PowerQuery verfügbar sind.

Nun müssen beide Tabellen miteinander verbunden werden …

Dazu wähle ich aus dem Navigator die Kunden Tabelle aus und klicke auf Abfragen zusammenführen:

pq_4

 

 

Nun sind die Tabellen verbunden – technisch ist das wie ein Left Join zu sehen. Über den Haken „nur Übereinstimmende Zeilen einbeziehen“ würde das einem Inner Join entsprechen.

pq_5Nun können die Spalten aus der rechten Tabellen hinzugefügt werden.

pq_6

Da ich hier lediglich die Bestellungen aus dem Jahr 2014 vor mir habe und das Ziel eine Auswertung pro Monat ist, transformiere ich die Spalte Bestelldatum über einen Rechtsklick in Monate um. Danach steht das Feld als Monatsnummer zur Verfügung.

Die Transformation kann nachträglich immer noch wieder zurückgestellt werden. An sich handelt es sich nur um eine Formatierung.

 

 

Im nächsten Schritt brauchen wir eine Gesamtsumme. In den Daten steht derzeit nur die Summe der Einzelposition und die Menge zur Verfügung. Daher fügen wir hier nun eine Benutzerdefinierte Spalte ein mit folgender Formel:pq_7

 

pq_8Nun markiere ich noch die nicht mehr benötigten Spalten und entferne diese:

 

pq_9Nun markiere ich die Spalten Name und Monat und klicke auf Gruppieren Nach – nun erfolgt die Gruppierung, bzw. Summierung auf den Gesamtpreis.

pq_10

Nach dem Klick auf Anwenden und Schließen befindet sich im PowerPivot Datenmodell die gruppierte und aufbereitete Tabelle. Diese kann ich nun in Excel darstellen….

 

Einführung in PowerQuery, Teil 1 von ?

thIn diesem Blog Eintrag möchte ich eine kurze Einführung/Tutorial in das Thema PowerQuery geben. Das ganze wird eine Serie, Teil 1 von ? – ganz in der Manier des Report Viewers. Schauen wir mal wieviele es werden.

In diesem Beitrag schauen wir uns den Import von Daten aus dem Web an und wie die Daten verarbeitet, transformiert und in das Modell geladen werden können. Weiterhin werden 2 Tabellen mit einander verbunden und als eine Liste später in Excel dargestellt. Soviel zum Ziel…

2014-06-28_21-19-21Starten wir auf einer Webseite. Dort liegt eine fiktive Preisliste die außerhalb der eigenen Umgebung liegt. D.h. diese Liste steht weder auf internen SQL Servern o.ä. zur Verfügung und liegt auch nicht unter der eigenen Kontrolle. Dennoch soll diese Preisliste in Excel bzw. PowerPivot verarbeitet werden. Um an solche Daten zu kommen, bietet sich das kostenlose Excel 2013 Addin „PowerQuery“ an.

2014-06-28_21-20-19In Excel wechseln wir auf den Ribbon Power Query und laden Daten aus dem Web. Dort wird die URL der Webseite eingegeben.

2014-06-28_21-21-12Nach dem Bestätigen werden uns 2 Tabellen angeboten. Auf der Webseite war schon zu sehen das die erste Tabelle die Preisliste darstellt. Wenn man sich nicht sicher ist, fährt man mit der Maus über die Einträge und bekommt dort schon eine Vorschau.

Mit einem Klick auf Bearbeiten wechselt man in ein eigenes PowerQuery Fenster um die Verarbeitungsoptionen festzulegen.

2014-06-28_21-24-03In dem Bereich „Angewendete Schritte“ wird eine Historie geführt. Das erste nach dem Laden ist immer das setzen, dass die erste Zeile die Überschriften der Zellen beinhaltet. Jegliche Schritte können hier manuell gelöscht werden. Da die Spalte Rabatt leider die Daten mit Punkten anstatt einem Komma in der Fließkomma Zahl trennt, muss dies beim Import geändert werden, da ansonsten dies in Excel als Tausender Trennzeichen interpretiert wird. Weiterhin muss das Prozentzeichen entfernt werden. Beim Preis wurde schon automatisch das Euro Zeichen entfernt. Über den Bereich Werte Ersetzen werden diese Änderungen vorgenommen.

2014-06-28_21-24-572014-06-28_21-25-29Weiterhin wird die Spalte noch als Datentyp Zahl definiert. Der ETL Prozess ist somit eigentlich abgeschlossen. Über den Bereich „Einstellungen laden“ können nun die Daten nach Excel und/oder PowerPivot (via in Datenmodell laden) übergeben werden.

2014-06-28_21-32-59Nun haben wir aber noch eine zweite Preisliste in einem anderen Format die hier dieser Liste angefügt werden soll. Ein Feature was in PowerPivot schonmal nicht möglich wäre, da wir so nur zwei Tabellen bilden könnten. Diese Preisliste unterscheidet sich in den Spalten. Die Rabatt Spalten fehlen völlig und die Beschreibung wie auch die Preisspalte sind auch noch anders benannt. Dennoch importieren wir einfach über PowerQuery die Daten:

2014-06-28_21-34-212014-06-28_21-35-14

 

 

 

 

 

 

 

Hier benennen wir erstmal die Spalten so wie in der vorherigen Tabelle indem man mit einem Doppelklick auf die Überschriften der Spalten klickt und die vorherigen Werte überschreibt.

2014-06-28_21-37-30Nun fügen wir einfach die beiden fehlenden Spalten noch der Tabelle hinzu damit die beiden Tabellen das gleiche Format/Modell haben. Als Standardwert tragen wir hier einfach eine 0 ein.

2014-06-28_21-40-37Nun nutzen wir das Feature „Abfrage anfügen“. Hier wählen wir die vorherige in PowerQuery verarbeitete Tabelle aus und bestätigen das Fenster mit OK.

Nun sind beide Tabellen in einer zusammengeführt. Über den Aktualisieren Knopf werden auch beide Tabellen aktualisiert, d.h. aus zwei Schritten wird einer.

2014-06-28_21-41-41Wenn nun die Daten nach PowerPivot oder Excel übergeben werden, liegen die Daten in einer Tabelle.

Fazit:
Mächtiges Tool was teils komplexe ETL Prozesse in den Self Service BI Bereich bringt und Anwendern die Möglichkeit bietet, Internet Daten schnell und einfach ohne IT zu verarbeiten.