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.
A Data Table is the table that contains information. So in those tables I need to load the information about required and returned supplies.
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
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
- 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
- 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.