#MakeOverMonday Week 28 2019
Today and yesterday I have been working on a second report with the data given by #MakeOverMonday.
My biggest challenge was getting the data in the right format to create a normal line or bar chart, with the years on the x-as and the country’s being the values. I dove into one of the other reports and found out, that the format of the data had been changed. The years where no longer in separated columns, but moved into 1 column and that is what was in my mind also. So first I worked in Excel and created the format needed to make the report I wanted. It takes time to put it in the right format.
Lesson learned: if I feel the data is not in the right format, change it first before putting time in making a report and than need to restart again.
While I was busy in Excel I was like, this most be possible in Power BI with the Power Query Editor. So I went back to Power BI and have been reading carefully and found the option: Unpivot Columns in the Transform menu and it did the trick in a click
After the data was in power BI in the format, I wanted I did some cleaning of the data
- Removing empty rows
- Chancing the value “:” into an empty cell. This value was used several times, I did not mean 0 because there where also zero’s in the columns.
- Changed “Germany (until 1990 former territory of the FRG)” into Germany, because all data was from 2009 and onwards.
- Filtered out the information about the 28 European countries together
- Changed “Total positive decisions” into “accepted”, because that fitted nicer in the graph of denied and accepted
This time I have build a 3 visual report
Visual 1 shows the total asylum requests in Europe over the year by country.
I noticed a peak in Hungary in 2015 and dove into news articles to find out when Hungary closed the borders for Middle east refugees with Croatia and read this was end 2015. So this news fact is visible in the graph.
Visual 2 shows how many decisions have been made. In Germany you can see that a request and a decision is not always in the same year.
Visual 3 shows the accepted and denied request. I have chosen for a clustered column chart instead of a stacked column chart. Because here it is easier to see what has been more.
For the 3 visuals I made the Y-ax the same scale.