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?

And to make it easier for the user I formatted cells to be filled with a 3D effect – a trick I learned here. I decided to use different colors for ‘Employee data’ and ‘Document data’. For the rest, some kind of a validation rule has been applied using standard settings of the Data Validation tool in Excel. As you see from the picture above only the Name is a free text field. This is the design of the table we agreed on:Īnd here is the desired outlook of the entry form: Step 1: Create the entry formĬreating the entry form was fairly easy. It is important that each row in the table should have a unique ID number (simple count 1, 2, 3 … is fine).Users prefer to fill data through a predefined form with drop-down lists where applicable.Users should be able to manually correct data in case of error.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.The solution should be the cheapest possible. 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.A simple table that contains key information about the documents (employee name, department, date of issuing and expiring, etc.) is needed.These documents gave certain rights to respective employees and had different expiry dates.Īfter a short conversation with my colleagues I made some notes that shape the constraints of this task: Business CaseĪ 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. The solution file of this article, Register attorney with PQ.xlsm, is available for DataChant subscribers in the Subscribers folder (subscribe here). In this case, its versatile capabilities are utilized in an unusual way to solve a common business. Traditionally Power Query is perceived as a tool for data extracting, cleaning and shaping for further analysis. This article will demonstrate how Power Query can be used for maintaining a simple database/register.

Our guest today is Kolyu Minevski, Director, Financial Management and Reporting at Fadata Group.

Power query excel 2019