Skill Matrix
A skill matrix is what companies use when tracking the relative skill level of their employees. It is a useful skills assessment process for identifying skill proficiency of employees in order to better manage project assignments and conduct training.
The company I interned for does the skill matrix manually with Excel and when they incorporated the use of SharePoint they just put the Excel files into SharePoint. My supervisor does this manually. It was a tedious task and he needs to make a report annually using those Excel files. He tracks at least 50 software engineers across four department sections. He gave me a task of compiling the Excel files and adding a marker/sign within an Excel to notify him of his last modification. A date of when and what he modified last.
We had a talk to how he usually use those files and I understood the processing he does manually. I then suggested to make him a Skill Matrix app using PowerApps and incorporating in the the use of SharePoint List.
Design Phase
Kanzen
The company I interned for was a Japanese company. This is actually their version of a gap analysis and I had to make them in order for my project to be approved.
This is just an example of one of the processes that I need to make. I had to make something like this for all process involve. I opt to only recreate one of them because I just needed to showcase that I do and will make them if asked to.
At the company, I had to present the project to my supervisor, section, and department manager. If they deemed the project to be a worthy endeavor, they will ask for ask for a timeline for the project. A Gantt chart. Given that I had absolutely no prior experience on PowerApps and it was my first project with PowerApps I passed a Gantt chart that will give me at least 6 weeks to complete.
Data Structures
I always start any project that has data with an Entity Relationship Diagram. ERD helps me determine the starting point of the projects I am going to create. It will also dictate the UI and processes of my applications. Basically I use it as the base for my design, as I find that building around a 'database' is easier than creating the UI first then shaping the database to fit the UI.
I developed this habit from my experience with coding using C++. I got to the level where I use objects, inheritance, and polymorphism in my projects, and they got so big I had trouble keeping track of the objects and any changes in the data structure was almost equal to a complete overhaul.
Developing that habit saved me a lot of time and made my projects easier to modify.
Wireframe & Wireflow
After I create an ERD, I always create an initial UI design for my starting page or the page where you can access every page.
Because PowerApps is a low-code app where you can drag and drop controls, I only make the dashboard or 'Main screen'. I do this to determine the wire-flow or screen-flow of the application I'm going to make.
The rest of UI design I do inside Power Apps.
After creating the initial 'Main screen'. I then create a wire-flow so I can determine the approximate number of pages or 'screen' that I need to make.
This is just for the initial design and something for me to start with. Any and all modification is expected throughout the development phase.
Development Phase
Building the data source
The moment I was approved to proceed to create my project I was given access to the assessment files. A total of 54 excel files. This was a department wide application. The department has 4 sections and a total of 54 employees and I had the pleasure of translating the data inside the excel to a SharePoint list. FUN!
Attempt #1
Made a SharePoint list and used the create from excel template. It was messy and didn't work as intended. Failed!
Attempt #2
Manually typed the employees one at a time. Took me more than an hour. It was inefficient and put me on a daze for the rest of the day. Mind you that was just employee info and not the skills table for each employees.
Failed!
Attempt #3
Next day. I used Power Automate. I created a flow that will take the excel files 1 by 1 and get the data to from the tables and put them on the List on SharePoint.
I renamed the excel files to numbers 1.xlsx up to 54.xlsx. Put them all in a single folder and run the flow I created.
It worked! Except it didn't really. There was an error. It turns out a NULL column in any row will force the flow to skip that row and the row will not get transferred to the List.
Attempt #4
I already built the UI at this point and it was inside the App that I noticed the error from my 3rd attempt. There were missing skills. It's not a lot but it was still in up to 50 of rows missing. I could type them manually but it's not good practice to do so. Also I'm lazy, I don't want to do that, so I need a way to transfer them without errors in the laziest way possible. With Power Automate.
I can't find a workaround to avoid those row with null columns. Then it hit me. I don't actually need the columns with null data. I just need the columns. So I created a flow, that will take each employee and the skill table and have the flow create the rows for me. Downside Is the columns with integers will default to zero but I can just set the data using the app I created. Not only can I work around the problem, I can also test my application at the same time.
For every row in the Employee list, there are 37 rows in the Employee Skill list. The ones I'm showing here is just a recreation. There are 8 profiles, and only 13 Skills per profile so there's not a lot of rows. The original one has 54 X 37 rows in the Employe Skill list.
UI Development
This is the main screen. I created a log in screen initially but since only a few person will have access to it I decided not to add the log-in screen.
As shown in the wireflow I created, this is the screen where you can access other screens.
This is an image map. Hover over parts of the image and the codes I wrote will be shown below.
The gallery is simple which can be sorted with first name, last name, and job role on the search bar.
Information shown can be customized according to stakeholder needs.
Notable Challenges
There were multiple challenges I faced during the creation of this application. One of them was the migration of data from excel to SharePoint List which I already shown above. The creation of UI is fairly simple since this is a low-code application maker and I believe that, what's separates a good developer from the rest is the problems they face and the solutions they come up with.
Below I've listed three notable problems to showcase my process, my problem solving skill, and relative knowledge on PowerApps. I've ranked them from according to their difficulty with red being the hardest.
New/Delete Profile
This was one of the last part that I made because I thought it was easy. So, when I finally got to making this part, I was taken a back.
Because of the what the application should do and how data was managed, this part presented a real challenge that required some creativity on my part.
Update Target Levels
I have knowledge on how SQL as a database works. Since I had no prior knowledge about PowerApps and little experience with SharePoint List I assumed it works the same way as an SQL database. So I made this last because I thought it would be easy.
SQL is a relational database. SharePoint List is not. I had to learned that the hard way.
Conclusion
I started this without any prior knowledge on how to use PowerApps. I assumed that since I used to code in C++ at a pretty decent high level that I can just use my experience with C++ as the base for creating a PowerApps. I was wrong, it's pretty stupid to think that the data structures that I learned with C++ can be used here.
Let me elaborate further. Fundamentally, the data structures are similar but the execution and they way they are used and created are very different from each other. I had a pretty steep learning curve with this because I had to forget everything I know about traditional Object-Oriented Programming languages to learn how to create with this. This is very different from what I have used and learned before and at the start i was really hard for me to get acclimated with this type of app creation.
There are still a lot of things about PowerApps that I am confused and/or doesn't fully know how to use as high-level or as efficient as I would like it to be. I still need a lot of things to learn and master but this project has pushed to find some of the limits of PowerApps on what and how to create it and use it.
I feel confident that even with my limited experience with PowerApps that I can immediately be helpful towards any project.
Screens
