Excel 2013 and the Flash Fill Function (ETL for Beginners ;-) )

Off topic but really good to combine with PowerPivot or PowerQuery: The flash fill function.
With this feature in Excel 2013 you can automatically extract text into columns. It works better than the text in columns feature or Left, Right or Find functions in Excel or PowerPivot for me.

Example:

This column contains surname, middle names and last names into one column. First you must convert this data into a table:

10-04-_2015_15-01-42

Next add a column “surname” – please do not add the other columns. Sometimes Excel is confused by the other columns…
Then type in the new column the first surname “Franz”. In the next row start typing the next surname “Karl”. Excel writes automatically the other names into the cells. In this case you can also use the Flash Fill Function in the Ribbon “Data” – but this function will not work by the middle names.

10-04-_2015_15-04-55

 

 

 

 

 

 

 

Now add a new column middle name and type in the first data cell the first middle name “Dieter”. If your data have no double name in the first rows, sometimes you must type much more names if Excel use the Flash Fill Function.

10-04-_2015_15-05-40

 

 

 

 

 

 

 

 

In the last row you can see the surname in the middle name column. If you have middle names or data with more text parts it is sometimes not perfect! The last step is to add the last name column. The data has in the first 2 rows some different data – a single last name and double name. So you must type 2 rows till the Flash Fill Function takes effect:

10-04-_2015_15-06-08

Now you can add this table into your PowerPivot or PowerView model. Enjoy this feature!

More information: http://blogs.office.com/2012/08/09/flash-fill/

off topic: Office 365 / Office 2015 Click & Install

office365logoorange_webBeim installieren von Office 365 wie auch der Preview von Office 2015 habe ich immense Probleme gehabt. Einerseits konnte ich teilweise das Setup nicht starten, da ich laut Frontend zu wenig Speicherplatz oder keine Interverbindung hätte, oder das Setup brach mittendrin ab, mit der Meldung das eine Datei nicht gefunden werden konnte. Die Logdateien gaben hierzu ebenso wenig Hinweise. Das Problem tritt bei mir mit einem frischen Windows 8.1 Rechner sowie mit einem Windows 10 Preview (March Update) auf. Nachdem ich nun den Windows 10 Rechner neu aufgesetzt habe und die Windows Firewall sowie den Windows Defender deaktiviert habe, läuft nun die Installation. Nebenbei bemerkt, ist auch meine Internetverbindung nicht die schnellste und stabilste.

Update: Das Problem löste sich nur temporär auf. Nach weiterer Recherche liegt es wohl an meinem Speedport W 723 V Typ A. Die Installation wurde weiterhin unterbrochen. Nachdem das Problem mit weiteren Geräten im Netz nachvollziehbar war, und ein Tethering mir zeigte, dass es nur auf Rechnern auftritt die in diesem Netz sind, habe ich kurzerhand den Router gegen eine aktuelle Fritz! Box ausgetauscht – sieht da, es funktioniert. Wenn man den Speedport als Modem nur verwendet und die interne Firewall somit aus dem Spiel nimmt, funktioniert es auch. Das ändern der DNS Adressen hat auch nichts gebracht – Grausam!

Nachtrag zu Visual Studio Online

vs13onlineIn diesem Artikel habe ich beschrieben, wie man das Visual Studio Express 2013 mit den Data Tools installiert um so BI Projekte online verwalten zu können.

Bei der letzten Installation habe ich bemerkt, dass nach dem installieren der Data Tools sowie dem nachträglichen installieren des Visual Studio Express Web Edition, dass beim Starten der Data Tools das Anmeldefenster mit dem Microsoft Account einfach Weiß bleibt. Um dieses Problem zu beheben, startet man das Visual Studio Express Web Edition und installiert das Update für die Data Tools nach. Anschließend ist ein Login mit dem MS Konto und ein verbinden mit dem Visual Studio Online, bzw. dem TFS möglich.

 

English Version:

In this article I have described how Visual Studio Express Web Edition is installed with the Data Tools 2013 by BI projects to be able to manage online.

In the last installation I‚ve noticed that after installing the Data Tools and Visual Studio Express Web Edition, when you start the Data Tools, the login window with the Microsoft account simply remains white. To resolve this problem, open the Visual Studio Express Web Edition and load the update for the Data Tools after. Then you can log in using the MS account and work with the Visual Studio Online / TFS.

Wasserfall Diagramme mit PowerPivot

PowerPivot_Logo(english version below)
Nachdem ich bisher Wasserfall Diagramme manuell in Excel erstellt habe, möchte ich hier zeigen, wie das auch in Verbindung mit PowerPivot für Excel funktioniert. Die Datengrundlage ist die gute alte Northwind Datenbank. Um ein Wasserfall Diagramm zu erstellen, benötige ich folgende Informationen:

  • Aktueller Umsatz
  • Umsatz des letzten Monats
    • Verringerung und
    • Vergrößerung des Umsatz
  • Zeit Informationen

Mit folgendem Query lese ich die Daten und löse das Pivot auf, damit ich die Daten besser in PowerPivot verarbeiten kann:

SELECT
 YearNumber
 , MonthNumber 
 , Amount
 , AmountType
 , CASE AmountType
  WHEN 'BaseValue' THEN 1
  WHEN 'Increase' THEN 2
  WHEN 'Reduction' THEN 3
 END As CategorySort
FROM ( SELECT 
  CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As Reduction
  , Data.SalesAmountLastMonth - 
   CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As BaseValue
  , CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  > 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth)  
    ELSE 0
   END As Increase
  , Data.MonthNumber
  , Data.YearNumber
 FROM (
  SELECT
   MONTH(Orders.OrderDate) As MonthNumber
   , YEAR(Orders.OrderDate) As YearNumber
   , SUM(Quantity*UnitPrice) As SalesAmount
   , (
    SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1
    INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID
    WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) 
    AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))
   ) As SalesAmountLastMonth
    FROM [dbo].[Orders]
    INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID
    WHERE Year(Orders.OrderDate) = 1997
    GROUP BY 
   MONTH(Orders.OrderDate) 
   , YEAR(Orders.OrderDate)
  ) data
 ) t
 UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount
ORDER BY YearNumber, MonthNumber

 

Nachdem diese nun eingelesen wurden sind, habe ich zu jedem Monat 3 Zeilen mit den notwendigen Informationen. Nun lasse ich die jeweilige Kategorie noch sortieren, damit der Basis Wert im Diagramm unten liegt. Übergibt man nun die Daten in ein gestapeltes Diagramm, sieht das folgendermaßen aus:

1

 

2

 

 

3

Nun müssen lediglich folgende Anpassungen gemacht werden:

  • Die Kategorie mit dem Basis Wert ohne Farbe einstellen
  • Verlustwerte rot färben
  • Gewinnwerte grün färben
  • Die Beschriftungen ausblenden

4

Voila! Fertig ist das Wasserfall Diagramm mit Hilfe von PowerPivot.

 

Waterfall Chart with PowerPivot

 

After I’ve created waterfall charts manually in Excel, I want to show here how this works in conjunction with PowerPivot for Excel. The data is from the good old Northwind database.

To create a waterfall chart, I need the following information:

  • Current sales
  • Turnover last month
  • reduction and increase in the sales
  • Time information

With the following query I read the data and make a unpivot so I can better handle the data in PowerPivot:

SELECT
 YearNumber
 , MonthNumber 
 , Amount
 , AmountType
 , CASE AmountType
  WHEN 'BaseValue' THEN 1
  WHEN 'Increase' THEN 2
  WHEN 'Reduction' THEN 3
 END As CategorySort
FROM ( SELECT 
  CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As Reduction
  , Data.SalesAmountLastMonth - 
   CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As BaseValue
  , CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  > 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth)  
    ELSE 0
   END As Increase
  , Data.MonthNumber
  , Data.YearNumber
 FROM (
  SELECT
   MONTH(Orders.OrderDate) As MonthNumber
   , YEAR(Orders.OrderDate) As YearNumber
   , SUM(Quantity*UnitPrice) As SalesAmount
   , (
    SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1
    INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID
    WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) 
    AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))
   ) As SalesAmountLastMonth
    FROM [dbo].[Orders]
    INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID
    WHERE Year(Orders.OrderDate) = 1997
    GROUP BY 
   MONTH(Orders.OrderDate) 
   , YEAR(Orders.OrderDate)
  ) data
 ) t
 UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount
ORDER BY YearNumber, MonthNumber

After I imported the data, I have 3 lines for each month with the necessary information. Now I let the respective category yet sort, so that the base value is in the chart below. Then we can create a stacked bar chart:
1

 

2

 

 

3

 

Now only the following adjustments must be made​​:
– Set the category to the base value without color
– Reduction is set to red
– Increase is set to ​​green
– Hide the Labels

4

Voila! Finish is the waterfall chart using PowerPivot.

 

Stichtagsinformationen zu einem Datum

uhr(english Version below)

Durch einen Eintrag auf http://sqlmag.com/t-sql/cheat-sheet-calculating-important-dates habe ich mich inspirieren lassen, eine kleine Funktion aus den Statements zu erstellen, die zu einem Datum Informationen liefert. Ganz praktisch bei Reporting Services oder im PowerBI Umfeld, wenn man in einem Bericht verschiedenen Zeit Informationen zu den Daten anzeigen will, bspw. aus welcher Zeit-Periode abgefragt wurde wenn ein Report bspw. nur einen Stichtag aufnimmt, aber alle Daten aus dem aktuellen Jahr selektiert oder dem Quartal …

 

By an entry on http://sqlmag.com/t-sql/cheat-sheet-Calculating-important-dates I’ve inspired me to create a small function of the statements that delivers information to a date. Useful in Reporting Services or in PowerBI environment, if you will, in a report several times to display information about the data. For example, if a report only takes a date as a parameter, but selects all data from the current year or the last quarter …

Funktion / Function:

CREATE FUNCTION dbo.InformationForADate(@QualifyingDate DATETIME)
RETURNS TABLE 
RETURN (
	SELECT
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) - 1 , '19000101')) AS [FIRST DAY OF LAST YEAR], 
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate), '19000101')) AS [FIRST DAY OF This YEAR],
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 1 , '19000101')) AS [FIRST DAY OF NEXT YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate), '19000101'))) AS [LAST DAY OF Last YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 1 , '19000101')))  AS [LAST DAY OF This YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 2 , '19000101'))) AS [LAST DAY OF NEXT YEAR],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) - 1, '19000101')) AS [FIRST DAY Previous MONTH],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate), '19000101')) AS [FIRST DAY CURRENT MONTH],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 1, '19000101')) AS [FIRST DAY NEXT MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate), '19000101'))) AS [LAST DAY Previous MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 1, '19000101'))) AS [LAST DAY This MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 2, '19000101'))) AS [LAST DAY NEXT MONTH],
	(SELECT DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Yesterday],
	(SELECT DATEADD(d, -0, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Today],
	(SELECT DATEADD(d, 1, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Tomorrow],
	(SELECT DATEADD(ss, (60*60*24)-1, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [11:59:59 Yesterday],
	(SELECT DATEADD(hh, 12, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [Noon Yesterday],
	(SELECT DATEADD(ms, (1000*60*60*24)-2, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [11:59:59.997 Yesterday]);

Abfrage / Query:

DECLARE @QualifyingDate DATETIME = GETDATE();
SELECT * FROM dbo.InformationForADate(@QualifyingDate)

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