Excel examples for your work, sports and more.

29 Mar 2022

Tableau report of Ukraine-refugees and destination-countries

#83: Tableau report of Ukraine-refugees and destination-countries

I made already a report 'Ukraine-refugees and destination-countries' with Microsoft Power BI (see this post) and Google Data Studio (see this post), and this post is about a report I made with Tableau Online (a free 2 weeks trial version).

Tableau has several (BI-)products, see:

https://www.arkatechture.com/blog/tableau-101-the-difference-between-tableau-products-plus-infographic

I already worked once with the free Tableau Public (see this post about a Covid-19 dashboard), and this time I chose to use Tableau Online, the hosted version of Tableau Server, so no need to install any software.

As a datasource I wanted to reuse the Google-Sheet for my Google Data Studio report (see this post), but unfortunately it is not possible to use a doc from G-Drive:

With Tableau Public, I could use a G-Sheets doc as a datasource for my (Covid-)report.

Anyway, I downloaded the 3 tables (3 worksheets) from G-sheets to CSV-files and used these files for my Tableau-report. So the data in this report is not automatically refreshed (as it is for the Power BI report and Data Studio report), but has the data from the UNHCR-dataportal as of today (29/3/2022).

I wanted to share my report on https://public.tableau.com/s/ , but to do this, I had to use Tableau Public. And I had to embed the datasources (3 CSV-files) of my report, using Tableau-extracts:

https://help.tableau.com/current/online/en-us/datasource_extract.htm

For my post on Tableau Public, see:

https://public.tableau.com/views/UkraineRefugees2022/UkraineRefugeesDashboard?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link

Embedded report

PEACE TO UKRAINE!

Source:

https://euromaidanpress.com/2014/03/02/russian-designer-creates-icon-for-peace-in-ukraine-using-the-shapes-of-the-national-symbolic/

References

https://public.tableau.com/views/UkraineRefugeeAnalysis/Sheet2?:language=en-US&:display_count=n&:origin=viz_share_link

https://public.tableau.com/app/profile/andriantseheno.tiana.val.riane/viz/UKRAINEREFUGEESBINTITIANA/Tableaudebord1

https://public.tableau.com/app/profile/info.unit/viz/UkrainesHumanitarianCrisisMapv2/UkraineIDPmap

https://public.tableau.com/app/profile/.45545509/viz/UkraineWarDataVisualizationsDashboard/1_2

Downloads

Tableau-report

27 Mar 2022

Google Data Studio report of Ukraine-refugees and destination-countries

#82: Google Data Studio report of Ukraine-refugees and destination-countries

The war between Ukraine and Russia is already going on for more than a month now, and the number of Ukraine-refugees who fled to a neighbor-country is close to 4M now. Also another 6M people fled to other safer regions in Ukraine. So in total aprox. 10M have left their homes now. Of them aprox. 4M are children, which is about half of the country's children, see e.g.:

https://www.washingtonpost.com/kidspost/2022/03/26/war-ukraine-has-forced-half-nations-children-flee-their-homes/

https://www.bbc.com/news/world-60555472

A previous post was about a Power BI report that I made with as a source UNHCR-data of Ukraine-refugees, see:

https://worktimesheet2014.blogspot.com/2022/03/power-bi-flow-map-of-ukraine-refugees.html.

In this post I made a similar report, but now with Google Data Studio:

https://datastudio.google.com/reporting/53d717be-f008-49b9-bc67-a2daeece66b7

The data-source of this report is a Google-Sheets that I made:

https://docs.google.com/spreadsheets/d/e/2PACX-1vSX2Oxyqwy6qA2RBRkBkmvlEu_npVea1ZPp6bE7AWBxue6rRIUhL2r_-53iQXyLqPoRH7LyvLMlw4S3/pubhtml

Here I import the JSON-files of the UNHCR data-portal. G-Sheets does not have a built-in function do do this, but there is a G-Sheets extension for this which I used:

https://nodatanobusiness.com/resources/importjson-your-first-importjson-function/

https://www.youtube.com/watch?v=EXKhVQU37WM - IMPORTJSON Function - Google Sheets Tutorial - How to Import JSON feed to Spreadsheets

NB: This add-on has a limit of #refreshes per day (100), but it looks there is a workaround:
https://discourse.gbif.org/t/api-importjson-spreadsheet-quota-limit-reached/2218

To format this data in table-format and in a separate worksheet (needed for Google Data Studio), I defined named ranges and used the QUERY-function:

https://infoinspired.com/google-docs/spreadsheet/learn-query-function-with-examples-in-google-sheets/

https://www.youtube.com/watch?v=kQ7DKx3eZQg - Create a Data Table in Google Sheets Like Excel

PEACE TO UKRAINE !

Source:

https://euromaidanpress.com/2014/03/02/russian-designer-creates-icon-for-peace-in-ukraine-using-the-shapes-of-the-national-symbolic/

References

https://www.statista.com/study/86697/russia-ukraine-conflict/#professional

https://www.forbes.com/sites/rashishrivastava/2022/03/15/how-one-google-doc-is-helping-thousands-of-ukrainian-refugees-navigate-borders/?sh=6c2fd8fed18e

https://twitter.com/Schwarzenegger/status/1504426844199669762

https://eacnur.org/es

Embedded Report

13 Mar 2022

Power BI dashboard with Flow Map Ukraine-refugees and Map NATO-countries

#81: Power BI dashboard with Flow Map Ukraine-refugees and Map NATO-countries

Today is the 18th day of the war between Ukraine and Russia and there are now 2.6 Ukraine-refugees.

In my previous blog-post, there was a live-tracker of the number of refugees based on this page, and I saw it was not updated, but that is fixed now. I also added some elements to the report, and some more details to that post.

In this post I wanted to combine the Flow Map of this live tracker report with the report'
'NATO-countries Map from this post.
Combining reports (or elements of it) is something you can do with Dashboards

Here is the result:

Note that on 2 tiles (coming from the report with the live-tracker of the number of refugees) you can see when it was refreshed.

You can also add tiles with other things than a link to a report, see e.g. the tile with a link to:

https://en.wikipedia.org/wiki/Russo-Ukrainian_War

See also the sample-comment I made, and by using a tag @.., an email is send to that person.

Update 15/3/2022:

I just saw that the Power BI report on the UNHRC data-portal changed the visualization from bubble-map to flow-map, and a nicer one than that of the custom-visual I used:

PEACE TO UKRAINE !

source pic:

https://euromaidanpress.com/2014/03/02/russian-designer-creates-icon-for-peace-in-ukraine-using-the-shapes-of-the-national-symbolic/

References

https://www.unhcr.org/nl/2022/03/dataportal-cijfer-vluchtelingen-oekraine/

https://www.theatlantic.com/photo/2022/03/photos-ukrainian-refugees-say-goodbye-home-and-family-members/626964/

https://news.sky.com/topic/data-and-forensics-9532

https://www.youtube.com/watch?v=yzNxLzFfR5w - Winter on Fire: Ukraine's Fight for Freedom | Full Feature | Netflix

5 Mar 2022

Power BI Flow Map of Ukraine refugees and destination countries

#80: Power BI Flow Map of Ukraine refugees and destination countries

Update 13/5/2022:
I made some improvements in the report, see Embedded report (at bottom), or see my post on the Power BI Data Stories Gallery here.

Today is the 10th day of the war between Ukraine and Russia and there are already over 1M Ukraine-refugees.

I saw here a flow map which shows to which countries they flee:

https://www.bbc.com/news/world-60555472#:~:text=Which%20countries%20are%20Ukraine's%20refugees,gone%20to%20Russia%20and%20Belarus.

https://www.aljazeera.com/news/2022/2/28/russia-ukraine-crisis-in-maps-and-charts-live-news-interactive\

I made something similar in Power BI, using the custom-visual Flow Map, for more info, see:

https://weiweicui.github.io/PowerBI-Flowmap

with as data source a Jason-file from the UNHCR site:

https://data2.unhcr.org/population/get/sublocation?widget_id=283559&sv_id=54&population_group=5459,5460&forcesublocation=0&fromDate=1900-01-01

This JSON-file I found here:

https://data2.unhcr.org/en/situations/ukraine

Below the map you can see a JSON-feed, with URL:

https://data2.unhcr.org/population/get/sublocation?widget_id=284488&sv_id=54&population_group=5459,5460&forcesublocation=0&fromDate=1900-01-01

and to view this file, you can use this nice (free) online JSON-file viewer:

https://jsonformatter.org/json-viewer

And I conclude this blog-post with the same picture as in the last post.
So as you can see in the URL, it is made by a Russian designer, wishing

PEACE TO UKRAINE

https://euromaidanpress.com/2014/03/02/russian-designer-creates-icon-for-peace-in-ukraine-using-the-shapes-of-the-national-symbolic/

Interesting links

https://www.nytimes.com/2022/03/01/world/europe/ukraine-war-migration.html

Download

Power BI report

Embedded report

2 Mar 2022

#79: Power BI Map of Europe and its alliances NATO EU Euro

Because of the war between Ukraine and Russia, and the importance of NATO-membership for especially East European countries that border with Russia as e.g. the Baltic states (Estonia, Latvia and Lithuania), I was wondering if it would be possible to make visualization in Power BI that can show for all European countries their membership in the most important military, political and economic alliances (NATO, EU and Euro), in one map. I came up with this solution, so a map with a pie-chart for each country that can have between 1 and 4 parts (Europe, EU, Euro, NATO):

REPORT

Note that in this pic, I filtered on field Indicator = NATO (see legend), which is the highlighted purple pie in the chart.

And if you zoom-out, you can see which other countries in the world belong to NATO.

And to my surprise I also saw that outside Europe, there are some countries were the Euro is used (ex-colonies of Europe, e.g. French Guiana):

DATA

When I googled to find a list with NATO/EU/Euro-countries, I found this site:

https://www.wolframalpha.com

Here you can formulate a natural language query using the Wolfram(computational) language
which does:

Compute expert-level answers using Wolfram’s breakthrough algorithms, knowledgebase and AI technology

and you get the result like this (I used the plain text version):

https://www.wolframalpha.com/input?i=countries+in+nato

MODEL

For the 4 datasets (Wolfram-output files), I made a dataset in Power BI, pivoted the 1-line-file (a list of countries) to have a table of countries, and added an indicator as e.g. 'NATO' for the NATO-dataset. And the last step was to 'union' these 4 datasets into 1 dataset:

Interesting links

https://www.washingtonpost.com/graphics/world/how-european-countries-are-bound-together/

https://euromaidanpress.com/2014/03/02/russian-designer-creates-icon-for-peace-in-ukraine-using-the-shapes-of-the-national-symbolic/

which this very nice (free to share) icon:

Download

Power BI-file

Power BI Embedded

(i-frame)

26 Dec 2021

Power BI Animated Bar Chart Race for Formula 1 season 2021

#78: Power BI Animated Bar Chart Race for Formula 1 season 2021

This post is about a Power BI report I made after Max Verstappen's victory of the Formula 1 championship 2021, a historic victory for the Netherlands as he is the first Dutchman to win a Formula 1 world championship. It was a battle between Lewis Hamilton and Max Verstappen, which was decided in the last race, and even in the last lap:
https://www.youtube.com/watch?v=MTe12fH2xtQ

Report

On this page: https://www.formula1points.com/season/season-progression/2021
I saw a line-chart which has a line-chart that shows how close Verstappen and Hamilton were until the last race. I added this chart to my dashboard (see FIG.1).

FIG.1: Line-chart timeseries cumulative points per driver

But to 're-live' the F1 2021 season, I also added 2 'racing bar charts', so an animation with the total points per driver over time:

*racing bar chart 1:

for this I used custom visual 'Power BI Animated Bar Chart Race', see e.g:
How to create Animated Bar chart race in Power BI

https://inovista.com/animatorFiles/demoProjects/BarRacePBI.html

NB: this visual has a limit of max 20 bars (while in F1 2021 season there were 21 drivers..)

FIG.2: Animated Bar Chart Race

NB: for a video-recording I made of this chart, see:

https://youtu.be/Fh4mRA_s8mo

*racing bar chart 2:

for this I used custom visual 'Play Axis', see video:
Guy in a Cube: Can we have ANIMATED Power BI visuals

FIG.3: Play Axis

Chart-1 is IMO the most fun one, as bar-charts (one bar per F1-driver) are moving up and down based on the driver's cumulative points after each race (date).
The date in this chart I had to format as a number (YYYYMMD), else the date-counter (bottom-right of chart) stayed 0. For more details, see:
https://community.powerbi.com/t5/Desktop/Period-Shown-in-Animated-Bar-Chart-Race/m-p/822985

NB: On the charts, I added this photo as a background:
https://commons.wikimedia.org/wiki/File:2015_Malaysian_GP_opening_lap.jpg
from https://commons.wikimedia.org/wiki/User:Morio
This file is licensed under the Creative Commons Attribution-Share Alike 4.0 International license

Data

The main data source that I have used is:
https://www.formula1points.com/season/season-progression/2021

Unfortunately, this page did not have the race-date, just the race/round-nr, and the date-field is a must-have for a racing bar chart. To get the race-dates, I found this F1-API:
http://ergast.com/mrd/

As a best practice, I also created a table for the Date-dimension, for how to do this, see e.g.:
https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables

My data source has the points per round, but for the charts, I needed the cumulative points.
To calculate this, first thing I did was to add a calculated Date-column to the Points-table, and as I created relations between this Points-table and the Rounds/Date table, I could do this with this DAX-formula:

Date = RELATED(Rounds_Dates[Date])

With this, I could use the DAX-pattern for the 'running total per group' (where in this case, the group is the driver, so for each driver, I needed to accumulate the points for the in total 22 races):

Points running total in Driver = 
CALCULATE(
SUM ( Points[Points] ),
FILTER (
    ALL ( Points ),
    Points[Date] <= MAX ( Points[Date])
        && Points[Driver] = MAX ( Points[Driver] )
) 
)

For more details about this DAX-formula, see e.g:

https://stackoverflow.com/questions/30072341/dax-running-total-multiple-critiera-grouping

https://www.daxpatterns.com/cumulative-total/

There were some other issues with the data source (e.g. race-points sometimes had a red star (*),

or for round 8, the country-name was not correct. If someone knows a better site with Formula-1 data, please share, for if I would make for the new season a new report (only if Verstappen wins ;)

Interesting reads

https://community.powerbi.com/t5/Data-Stories-Gallery/Formula-1-analysis-1950-2021/m-p/2052947

https://towardsdatascience.com/formula-one-extracting-and-analysing-historical-results-19c950cda1d1

https://www.linkedin.com/pulse/data-analysis-decision-making-formula-1-part-1-abhishek-kumar/

Downloads

Power BI file

Embedded report

6 Jun 2021

#77: Power BI report with ArcGIS map: "COVID-19 in USA"

This post is about a new Power BI report I made: "COVID-19 in USA", see FIG.1.

FIG.1: Power BI report COVID-19 in USA with (animated) ArcGIS map

In this report I used the ESRI ArcGIS map. I used this map also in my previous post, but this time I wanted to experiment with a nice feature of the ArcGIS map: animation of time-series. The dataset I used for this is open data from the New York Times:

https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-states.csv

NB: for more details about this NYT COVID-19 data, see e.g.:
https://developer.nytimes.com/covid
https://www.nytimes.com/article/coronavirus-county-data-us.html

The dateset's oldest data is from Feb.2020 and it is updated daily.
I have limited the number of data-points by filtering only the first day of the month. See FIG.2 for the Power Query I used for this:

FIG.2: NYT Dataset and applied transformations (in Power Query)

As a reference-layer for the map I used demographic data, e.g. US Median Age. And I also added an infographic with this data (median age), and also for the total population.

I made this video with the animated map and how to interact with it in Power BI, e.g by filtering by dimensions location (state) and time (year/month):

https://youtu.be/se5pLNCdo0M

NB: I used this nice free tool https://www.screencastify.com/ to make this screencast.

My Power BI report also has a time-slicer, so you could check e.g. : 'what was the total number of COVID-19 cases in 2020 (at 1/1/2021)', see FIG.3 (which also includes a screenshot of this site: https://datausa.io/coronavirus, which I used to check the numbers in my report)

FIG.3: total COVID-19 cases in USA in 2020

To know more about how ESRI ArcGIS maps are used in this pandemic, I can really recommend this video of ESRI:

COVID-19 & Crisis Management - A GIS Approach

And for a another nice video of ESRI in which they show how AI (Machine Learning) can be used to do COVID-19 predictions, see:

Modeling COVID-19 in ArcGIS

In this video, they say:

It has been amazing to watch how the GIS-community has done an amazing work in the COVID-19 response, from data collection, modelling and data-visualization..

I've also seen great initiatives in the Netherlands about which I wrote in earlier blog-posts, e.g.:

- data collection:

CoronaWatchNL - open COVID-19 data of Netherland, by Jonathan de Bruin of Utrecht University

https://dataverse.harvard.edu/dataverse/covid-19-eu
by Vyacheslav Tykhonov #4tikhonov

- dashboard:

ESRI Nederland COVID-19 dashboard with ArcGIS map