Guest Story: Maintain a simple database with Power Query

Our guest today is Kolyu Minevski, Director, Financial Management and Reporting at Fadata Group. You can contact him here.

This article will demonstrate how Power Query can be used for maintaining a simple database/register. Traditionally Power Query is perceived as a tool for data extracting, cleaning and shaping for further analysis. In this case, its versatile capabilities are utilized in an unusual way to solve a common business.

The solution file of this article, Register attorney with PQ.xlsm, is available for DataChant subscribers in the Subscribers folder (subscribe here).

Business Case

A few months ago the legal department in the company I worked for asked me to help them optimize the process of monitoring a specific type of legal documents. These documents gave certain rights to respective employees and had different expiry dates.

After a short conversation with my colleagues I made some notes that shape the constraints of this task:

•  A simple table that contains key information about the documents (employee name, department, date of issuing and expiring, etc.) is needed.
• Users have very basic knowledge in Excel (and actually feel very uncomfortable in this environment) which meant that the solution should be simple, user-friendly and involving minimal manual work in Excel.
• The client’s budget was limited. The solution should be the cheapest possible.
• MS Access would not be an option – it requires additional costs for licenses and users don’t know how to work in it at all.
• Users should be able to manually correct data in case of error.
• Users prefer to fill data through a predefined form with drop-down lists where applicable.
• It is important that each row in the table should have a unique ID number (simple count 1, 2, 3 … is fine).

This is the design of the table we agreed on:

And here is the desired outlook of the entry form:

Step 1: Create the entry form

Creating the entry form was fairly easy. As you see from the picture above only the Name is a free text field. For the rest, some kind of a validation rule has been applied using standard settings of the Data Validation tool in Excel.

I decided to use different colors for ‘Employee data’ and ‘Document data’. And to make it easier for the user I formatted cells to be filled with a 3D effect – a trick I learned here.

This is how it looks like:

Now we come to the tricky part of the solution:  When the entry form is filled – How can we add the data into a new row of the registry with a unique ID number?

Here is where Power Query comes into the picture.

Step 2: Create a query to add single row

We start by filling the form with some data. Then Select headers and data fields and go to Data -> Get & Transform -> From Table (Note: Excel 2016 has been used so there might be differences if your Excel version is different).

Here the table is imported in Power Query

Next steps are simple and straightforward:

1. Remove blank rows using the filter pane.
2. Transpose table so the information is in one row.
3. Use first row as a header.
4. Change type when necessary.
5. Add an index column, i.e. unique ID number.
6. Move the ID key to the beginning where it usually resides.

And here is our row is loaded in Excel:

So far so good, but there is an unexpected side effect:

Excel has added a header row to the input area thus spoiling our beautiful entry form. No worries, there is a very simple solution

Just click on the table and go to Table -> Design

Uncheck Header Row and Banded Rows and in Table Styles section use the plain style, i.e. the one that is in the most upper left corner.

Then select the cells where the Header row used to be and hit CTRL+minus sign.

Click OK and voila:

We only have to fill 2 cells with the yellow filling.

Step 3: Create Self-referencing Query to add a single row to the “database”

Now let’s return to the query we created. This query will rewrite the information each time we enter new data and hit Refresh. Of course, this is far from the desired solution. How can we overcome this?

The answer is a technique I learned from Matt Allington’s blog that is better known as ‘Self-referencing tables’. The idea is presented in the picture below:

As you see we need to create another query that uses as a source the table created with the first query and then append it to it. I know it sounds weird but it really works so let’s do it.

We go to Data -> Get & Transform and choose From Table.

Here are the two queries called “database” and “bridge”. Note that “bridge” loads data as a connection only!

At this point, they look exactly the same but this will change soon. First, let’s do the append. Go to Home -> Combine -> Append Queries and choose Append Queries.

In the menu that pops up choose “bridge” from the drop-down list and hit OK.

Here is the result:

But here is what happens when a new entry is added:

Our Index column is not exactly an index with unique values and the last entry goes on the top. Furthermore, we have duplicate rows.

Step 4: Fix the Index

We start by replacing the Index column in the “database” query with a Custom Column (called “Index”) that contains a fixed value.

This step creates the following M expression in the formula bar:

= Table.AddColumn(#"Changed Type1", "Custom",
each 1)

We will now replace the hardcoded 1 with some parameter that indicates the number of rows reached so far. This parameter can be easily obtained by counting the rows in the bridge table following these steps:

Right-click on the “bridge” query and choose Reference.

Select any column in this new query and go to Transform -> Statistics -> Count Values.

Rename the query to something meaningful, for example, I used “Max”.

As a result, we have a parameter that counts the number of rows reached before the new entry is added.

Now we simply go to the “database” query and amend the step where Index is added to look like this:

Or if you prefer you can directly amend the M formula in the formula bar to look like this:

= Table.AddColumn(#"Changed Type1", "Custom",
each Max+1)

The result – We have no duplicate values and our Index column contains correct values:

The last step would be to sort the table by Index column in ascending order and fix the format of the columns.

Step 5: Final touch

To make it easier for the users, I have added two buttons with simple macros assigned to them. The first button clears the form in case something wrong has been entered. The second button, “Submit data”, refreshes all queries and thus updates the table.

The final step is to protect the sheet without a password. This will allow jumping through the fields using the Tab key.

Closing Words

This article demonstrates that Power Query is a powerful tool that can help solve real business needs that go far beyond pure reporting and analysis of data. Excel users can easily dive deeper into Power Query (and Power Pivot). There’s nothing wrong with that as long as this is not at the expense of completely neglecting VBA and other useful tools that Excel offers. Combining various functionalities provided by Excel is the key to solving real business cases like the one described.

The solution file of this article, Register attorney with PQ.xlsm, is available for DataChant subscribers in the Subscribers folder (subscribe here).

All the names used here are completely fake and any similarities with real persons are accidental and unintentional.