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.
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)
Today I got a message
from twitter that there was a post with my name so I went to it. I was
wondering how did I contribute to this news. So, I followed up the link and
found a great website with a nice collection of Power BI news.
I started reading and at when I was at the topic “Business” I found a my blog about the asylum applications in Europa part 3.