Challenge myself to change a set of Excel files into a Power BI report; part 3

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.

Plaats een reactie