Algemeen

Wednesday, we received the last information about the hackathon. I made a Trello Board, connected with Planyway so I could view the agenda in that way and easily tick off when events where past.

Friday morning 7 AM was the time that I left to travel to Wageningen. The trip went smooth and at 9:00 I walked into the building, we started with coffee and tea.
At 9:30 we were officially welcomed and the four different challenges where presented
At 10.30 and at 11:00 we had the chance to talk to the challengers to find out more about the challenge. At home I had looked and thought that the Hendrix challenge was the coolest. During the presentations I also found the Lely & Rovecom Challenge interesting. At the end I did choose for Hendrix. 5 other people also choose this option and together we made a team.

Here a photo of the team and the challengers in blue.

When the teams were formed it was team for a lunch. After a good lunch we received the data and started exploring the data. Some with Python, some with R, I used Python and I used Power BI to get visual insides.

There was a lot of information in the data

The goal was: predict animal performance in warmer barns. So that is what we were looking for. I cannot tell much about our findings, because we explored the data, but we did not become owners of the data nor of the results.

At 16:00 we went to have a drink and a talk. We got and inspiration talk by Arthur Mol and information about the StartHub form WUR. 18:00 time for dinner and at 19:00 back to the data.

We were working closely to getter in different tools to find solutions and answers to the hypothesis. Just after midnight it was enough for me. I could barely keep my eyes open, lets even think. So, I unrolled my yoga math in another room and crawled into my sleeping bag.
Two other team members had left a little bit earlier to sleep home. 3 continued. 1 fell asleep in the chair while working and 2 fell on their sleeping mattress at 6:30.

At 8:00 it was breakfast time, this was served in the room we were, so at 7:50 the catering switched on the lights and we all were woken up.

After Breakfast time to pitch about the status of the project and back to sprint to the end of the project.

At 12:00 time to hand in all the work and time for lunch.

12:45 pitching time. 6 minutes per group. Whauw there were some stunning results. After the pitched it became thrilling; we had to wait for the jury to come with a result.

There were 3 prices for some terrific results. Unfortunally not for our result. On the other hand, the people form Hendrix were happy we had analysed the data and told them insides on it. They had not checked the data before this challenge.

For me I got what I had wanted to get. The experience of being present at a Hackathon. Seeing how other people deal with this. Working in a team. Meeting new people. Expanding my network with data lovers in the agri sector.

Thanks to Warren Dean for his blog, about how to make a Dashboard for LinkedIn Analytics

I found this blog at the beginning of September and I do want to analyse my LinkedIn information.
So, at September 6 I requested LinkedIn to send me my information. While waiting the 24 hours it takes LinkedIn to make my zip-file I started on exploring the Power BI file.

Warren used elements to create the top of the Dashboard. I used the lesson from a webinar (start at 39:40) and created a background in PowerPoint using Warren’s layout.
My last name is longer than Warren’s, so that gave a challenge to let it fit nicely. In the title I also have included since when I’m a LinkedIn member.

Fitting the slicer is the next challenge, that I defeated, by reshaping the slicer, I removed the title of the slicer. I also gave the slicer a fixed end date, because the data received from LinkedIn is static. When I ask an update from LinkedIn, I need to update this end date.

Waren used the default theme colours. I do not like to use the default so I used the website HTML color codes to find the RGB code for the LinkedIn logo and used that colour.

Top 20 Count of Company

The chart “top 20 count of company” shows in my dashboard more than 20 companies, so I checked the settings of the chart. I learned that it is because if there are more companies with the same count, they are also shown, so I change the title of the chart to “Where do the most of my connections work and what is there position” I made it a top 10.

Count of Share by Hour & Count of Share by Weekday

Here I changed the title and the colours.

LinkedIn Activity => Reactions per day

My linkedIn activity is not as high as Warren, so I decided to skip this cart and to change it into reactions per day.
Since this year we can choose for 5 types of reactions. I have given all 5 types the same colour and put them in a stacked column chart. When hoovering over the cart it is visible.

Article

I did not write article’s so I removed that chart.

The result

Designing a model

To make a report I need to have information in Power BI. I can just import the Excel files and see what I have. But that is not what I have learned taking the course by Avi Singh. First I need to design a model with Data and Lookup Tables.

So what information is in my Data Table and what in my Lookup Table.

Data Table

A Data Table is the table that contains information. So in those tables I need to load the information about required and returned supplies.

Lookup Table

A Lookup Table is the table with fixed values. So in those tables I need to load the information about the weight of each article, and a calendar table, because I might need the years.

Relationship between Data and Lookup Table

In the original information I have a column with item number so I need to get that in the Data Table and in the Lookup Table with the weights of the supplies. Oeps not every item has an item number so that cannot be the key. Need to work with the English name.

The original information has no date in the worksheet, only in the file name. so I need to create a column with the year so I can make an other relationship

Work in Power BI

First step: Making a table with all the requested items.

There are 4 Excel files for each year that we have logged information. This Excel files have a lot of worksheets. From each file we only need the worksheet with the requested totals. This sheet has rows with the products items and columns with the value per booth.

Per file a made 1 table in Power BI. I used Power Query Editor to

• Remove top blanc rows;
• Remove rows in the file that were blanc;
• Promoted the Headers, automatically the types of the columns were changed and I check and agree with the change;
• Made sure my columns had the right names (the same names over the four tables);
• Removed empty columns and columns I do not need the information from;
• Added 1 column with the date of the event and made the Data Type: Date;
• I unpivoted column, so that the Booth name became an Attribute column and the values became a Value column.

When I had this 4 Tables I used the Append Queries option to append this Tables into 1 Table and changed the name of the value column into Requested value.

At the start of the day, they booths receive 75% of the value of the items they have requested, so I made a new column for that.

Second step: Making a table with all the returned items.

For 2 years we also log the returns per booth to find out how much do they return at the end of the day. So next year we can advise them what to request.

These 2 files I also have put into 1 table, with the following steps

• Remove top blanc rows;
• Promoted the headers, automatically the types of the columns were changed and I check and agree with the change;
• Remove rows in the file that are empty;
• Every item is having two rows of values, in Excel Merge cells was used, in Power Query this ends up as a filled and empty row in the item column, so I used filled down as an option;
• Removed empty columns and columns I do not need the information from;
• Added 1 column with the date of the event and made the Data Type: Date;
• I unpivoted column, so that the Booth name became an Attribute column and the values became a Value column.

When I had this 2 Tables I used the Append Queries option to append this Tables into 1 Table and changed the name of the value column into Returned value.

For 2 years we also have on paper what they have requested extra during the day, this is not in a file.

Third step: Making lookup tables

I made 2 lookup tables. Information about the supplies and a Calendar Table. I’m still thinking about making a table with booth information.

Fourth step: Making relationships

• Made a relation between items required and the items lookup table;
• Made a relation between items returned and the items lookup table;
• For both data tables I also made a relation to the Calendar table.

Fifth step: First visualisation

In a column chart I want to show per item what they requested, what they got at start and what they returned at the end of the day. With using a filter for year and a filter for booth.

It is not working

I have been working on the above, but I came to the conclusion, that on the moment, with the knowledge I have of the Power Query Editor, I cannot shape the data into a nice table with working relationships.

The requested and what they got at the start of the day is nicely in the viz, the returns are not responding to the filter of year.

So, I go to create a new excel file which fits the requirements I need for PowerBI. I’m lucky I have access to the original data, so I can do this.

Continue in Excel

To start in Excel, I copied the Power BI table with the requested items and manually I have put in the return and while busy I looked for the paper work about the extra requested during the day. We only had 2018 so I have put those in as well.
There are some od numbers in the returns that do not make any sense. This might be explained by the fact that the counting is done at the end of the evening, by teenagers and it is done fast, because everybody wants to go home.

Back to Power BI

I have imported the new excel file and now I can make a Dashboard based on 1 table.

Description of the Excel files

Per year there are two Excel files and every Excel files has several worksheets. From each file I need one worksheet. The worksheet with the requested and additional supplies per booth and the worksheet with the returns per booth and the total stock in the evening.

The worksheet with the returns is first having columns that describe the different supplies, followed by columns that inform the user about how much a standerd amount way’s. Followed by booth name columns. Every article has two rows of information, weight and count. We need the count by article. By adding the forms from 2018 I realised that we had changed some articles and that we do not have the weight. While making the inventory has been done with the weight. We need to come up with a solution of that, even so if we keep the old system up and running, because we need to know the count of the items coming November.

The total required is a worksheet where in columns is written what every booth wants to receive. The evening before the FoodFair the booths are receiving 75% of what they have requested. During the day they can request for additional supplies, since 2018 that is also filled in on forms, that are put in one Excel file.

Both sheets are not complete and are having missing data, lets see how that works out when I import them to the Power BI Query Editor and transform it into the right format.

Description of the challenge

Every year in November there is the FoodFair of the church we are going to. My husband coordinates the supplies room and he has a set of Excel files (with formulas) to know how much supplies he uses a year, how much is left and how much he needs to buy again.

There are several booths with different food and different needs of supplies

At the end of the day we are left with a bunch of handwritten forms that tell us how many supplies have been given out and returned per booth. This handwritten forms go into the Excel file. And we do have a handwritten form with counted stock at the end of the evening. This form is also copied into a Excel file.

I gave myself the challenge to build a model from the Excel files that can be used on a yearly base and to make a report that shows what supplies are requested for and what is been given back per booth

As a by the way, writing this blog also teaches me more about possibilities in WordPress, because I want more than just a plain black text.

Start of the project

I see this as a real use case, because there is data in Excel in a reader friendly way, so that needs to be converted. The data is in several different files and collected by hand over more than one year.

I have domein knowledge, because I have been helping my husband with inputting data in the files and working with him during the FoodFair day’s.

The data that is there in Excel are two files a year. 1 file has the returns per booth and the stock at the end of the day. The other file has the requested supplies per booth. And there is a lot more information in the files on other sheets.

The forms from 2018 have not been put into Excel yet, so that is what needs to be done first.

The learning path Power BI started in april 2017 when I did my first Analyzing and Visualizing Data with Power BI online training in edX. At that moment my passion for Power BI was born.

Overtime I have been experimenting with Power BI and during my Data Science Professional Microsoft Track powered by Techionista learned more about Power BI.

In the past weeks I have been busy with exploring Power BI, by just diving into #MakeOverMonday challenges and working with trial and error. This way of working helped me to understand the program, but it also fed my hunger in getting to know more about Power BI.
In my search for video material I came across Avin Singh, he has a nice video series where he starts from the beginning with introducing Power BI. In this blog are my learning notes. I do advise you to watch the video series to understand Power BI better.

Before I started with this series I started with the most basic one “how to install Power PI”.
In this video he talks about the two way’s to install Power BI

• From the Microsoft website
• From the Microsoft store

My Power BI was installed from the Microsoft website and the plus point that you do not have to update it every month yourself, made me decide to deinstall that version and to install Power BI from the store. Within an hour I went back to the website version. I wanted to change something in the settings and I could not. I kept giving me a popup error message. I did not want to figure out way and decide that the website version and updating it manually every month work well in the past and I would keep it like that.

In Get Data he explains how to import data and how you can edit it with the query editor and how the query editor helps you with making a documentation so that somebody else (or yourself after some time) knows what has happened. Good documentation is important in Data Science, because in that why you can explain and prove to others what you have done.

In Relationships he tells you how to make a good data model. He explains about data tables and fact tables.

Up to know repeating of the knowledge I got from earlier training and self-exploring of Power BI.

In the section about DAX I get to learn some new facts.
A calculated column is a nice way for the human brain to see in the tables what is happening. It makes the file grow bigger in size.
We want to have a small file, because a Power BI file runs in memory of the computer.
The storage of a big file is not a problem, running a big file in memory can give a problem.
So it is better to use a measure. This does not make the file bigger. To write them you need to have more DAX knowledge. In his video’s he explains the basics.

There are two types of measures “Implicit” and “Explicit”.
Implicit means you take the column from the table and put it in the visualisation.
Explicit measure is a measure you made by using a DAX-formula.
He explains them and tells witch one is better to be used. To make that clear he really gives a nice picture why you are building a Power BI file.

The Data Scientist is the author from an Excel or a Power BI file, that file is being published in the cloud of Power BI and becomes the only file of truth. That file can be made excisable for consumers to use the data with the program they want.
And at that point it becomes very important if you have use implicit or explicit measures. Implicit measure can not be put in a pivot table in Excel, while explicit can.
A lot of users use Excel, so you need to build for their needs.

He tells a lot more about DAX measures and I start to understand it.

At the end he creates a report, that turns out to be a dashboard and online published. I did make the report he made, but did not publish it. I also changed how to put the text and images. I first made a jpg by using PowerPoint and loaded that as the background of the report (tip from Marc Lelijveld during a webinar I followed in July 2019)

Here is the created visual.