Datensammelwut der USA–gewinnbringend einsetzen

oder: warum ich auf http://earth-info.nga.mil/ und https://www.cia.gov wichtige Infos für meine Vorträge finde.

Derzeit stecke ich wieder in Vorbereitung für einen BI Vortrag der auch das Thema Power View umfasst. In jedem guten Demo sind Bilder enthalten. Die geben beim Vortrag einfach mehr her. Und so hatte ich die Idee, die Länder anhand der jeweiligen Flaggen zu selektieren.

In PowerView besteht die Möglichkeit Bilder in die Reports einzubauen, solange eine URL zu dem Bild angegeben wird. Für mein Demo muss ich nun von allen Ländern die Bilddateien herunterladen. Lokal einen Webserver aufsetzen und dann die Bilder bereit stellen. Hm…. Für drei Minuten Demo doch ein Aufwand. Das muss einfacher gehen.

Unter "https://www.cia.gov/library/publications/the-world-factbook/” stellt die CIA das World Factbook bereit. Nützliche Infos über alle Länder der Welt. Und eine wahre Goldgrube für Präsentationsvorbereitungen. Hier ist auch eine Liste alle Flaggen zu finden.

image

Wenn man genau nachsieht (Stichwort: Developertools) findet man die URL des Bildes: https://www.cia.gov/library/publications/the-world-factbook/graphics/flags/large/au-lgflag.gif

Jede Flagge eines Landes ist hier zu finden. Wobei im Dateinamen die ersten beiden Zeichen einen Länder-Code darstellen. Jetzt muss “nur” noch eine Liste aller Länder erstellt werden, und jeweils die URL angepasst werden.

Aber halt! die Liste gibt es sicher im Internet und aus der Website kann das geparst werden; es ist ja nur html Zwinkerndes Smiley

Die Liste gibt es, sie ist im Appendix des World Factbook zu finden. (https://www.cia.gov/library/publications/the-world-factbook/appendix/appendix-d.html). Dort ist auch erklärt, warum Österreich mit AU abgekürzt wird. Der nächste Schritt wäre ja die Liste abzutippen, doch das geht zum Glück mit PowerQuery einfacher. (siehe meinen Blog)

Leider ist das Format das die CIA gewählt hat für PowerQuery nicht zu lesen, aber auf der Seite findet man eine Erklärung woher der Code stammt und die Suchmaschine der Wahl findet recht schnell die Site http://earth-info.nga.mil und dort gibt es unter http://earth-info.nga.mil/gns/html/gazetteers2.html entweder eine PDF oder eine XLS Datei zum Download. Die XLS Datei ist leichter zu lesen. Leider ist der Inhalt der Datei nicht ganz den Normalformen entsprechend. Eher der “normalen” Form, die ein Excel Anwender häufig erstellt.

image

Und hier kommt nun PowerQuery ins Spiel. Diese Datei wir mit PowerQuery geöffnet und bearbeitet.

PowerQuery bietet die Möglichkeit Filter zu setzen. So suche ich mir die Bedingungen raus, um die Datensätze zu finden. Z.B: in Column3 muss “null” stehen.

image

Aber in Spalte 1 darf nicht “null” stehen:

image

und so geht es weiter. Weitere Filter, die überzähligen Spalten ausblenden und die Spalten umbenennen. Bis das Endergebnis so aussieht:

image

Das PowerQuery Script das entstanden ist, spiegelt wider wie einfach die Transformation war:

let
    Source = Excel.Workbook(File.Contents("C:\Users\martin\Downloads\GEOPOLITICAL_CODES.xls")),
    #"TABLE 2" = Source{[Name="TABLE 2"]}[Data],
    FilteredRows = Table.SelectRows(#"TABLE 2", each [Column3] = null),
    FilteredRows1 = Table.SelectRows(FilteredRows, each [Column1] <> null),
    FilteredRows2 = Table.SelectRows(FilteredRows1, each [Column1] <> "Table 2. Principal Administrative Divisions (Alphabetical by Entity)"),
    RemovedOtherColumns = Table.SelectColumns(FilteredRows2,{"Column1", "Column2"}),
    RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{"Column1", "Code"}, {"Column2", "Land"}})
in
    RenamedColumns

Nachdem die Abfrage erstellt und ausgeführt wurde, habe ich sie in ein PowerPivot Datenmodell geladen. Dort geht es nun weiter.

Im Datenmodell, erstelle ich eine berechnete Spalte mit der Formel:

="https://www.cia.gov/library/publications/the-world-factbook/graphics/flags/large/" & 
LOWER([Code]) & "-lgflag.gif"

Nachdem die Spalte erstellt wurde muss noch das Tabellenverhalten festgelegt werden. Der Menüpunkt ist im Ribbon unter “Erweitert” zu finden. Hiermit kann eingestellt werden, dass die Spalte mit der URL als Bild angezeigt werden soll.

image

In der Einstellung “Standardbild” ist die neue berechnete Spalte “Bild” auszuwählen.

Nun kann ein neues PowerView Sheet eingefügt werden, und sobald die Spalte Bild in eine Liste gezogen wird, wird die Flagge angezeigt. Der Import uns das zusammenstellen der Daten geht innerhalb weniger Minuten. Ich denke PowerQuery wird noch viel Spaß bereiten wenn es darum geht, irgendwelche Daten aus dem Internet in eigenen Auswertungen zu nutzen Smiley mit geöffnetem Mund

image

Kommentare sind geschlossen