PowerApps: 4 great tips in preparing the source data in Excel
This article explains how to prepare a PowerApps app-making project and 2 essential requirements and 4 great tips in preparing PowerApps source data with Excel, which help you avoid many errors later when working on PowerApps.
(Duration: 7:42)
＜＜ Related Posts ＞＞
- What’s ‘PowerApps’? Will it become mainstream in Process Improvement?
- PowerApps: Let’s make an actual app!【Process Improvement Innovation】
I started a PowerApps app-making project.
Hi, this is Mike Negami, Lean Sigma Black Belt.
I am recently into PowerApps very much. Then, I started a personal project using PowerApps. It’s named ‘Home Accounting Improvement Project’.
This will be a topic of my PowerApps video series that will show the way you can master PowerApps. Please remember when you start learning PowerApps, to come back and watch this series.
I wrote a project charter, so you can see what kind of project it is.
Problem Statement
I made a home accounting program with Excel that enables me to import downloaded statements from financial institutions, but after importing the data, I’ve failed to categorize income and expenses and haven’t utilized that data well.
Business Case
until now, I’ve had meetings with my wife for categorizing, but I’ll make an app with PowerApps that enables us to do that categorizing by ourselves so that we can prevent workflow stagnation.
The Value Stream Map of the target flow
The image above is a Value Stream Map of the target flow. Step 3 says “Each person finishes categorizing with the mobile app.” We’ll make that app with PowerApps this time.
By the way, Microsoft is asking us to call work on PowerApps ‘making’ instead of ‘development’. I guess they want to emphasize that we can complete the work easily in a short time. I agree with that 100%.
The design plan of this app
I also made a simple design plan, which shows each screen in my app, and the flow of information and necessary functions between them (See the image above). During this process, you can consider what kind of source data you need.
Screen 1 shows a list of our transactions with dates, payees and $ amounts that were imported from Google Drive. We also need input boxes for category names and notes.
It’s more convenient if we can select the category from a dropdown, so I’ll also import my category table.
We’ll sometimes need a new category, so I’ll put in a button which navigates us to Screen 2 on which there is a list of categories. You would enter a new category in the text box and press the ‘Add’ button to add it to the list. Press the ‘Back’ button to return to Screen 1.
After entering categories for transactions and pushing the ‘Save’ button, all the input data goes to the Google Drive.
For a project at work, if you make a good design plan and get acknowledgement from all your stakeholders, you can reduce re-do’s later. But PowerApps is so easy that it doesn’t even matter if you didn’t make a design plan at all, and you can start making an app right away.
Two Essential Requirements for PowerApps’ Source Data in Excel
Now, let’s do the first step of app making: preparation of the source data.
This is my source data for this app. I mentioned on the last video, it’s requirement to keep the data as a ‘Table’.
Simply select the data range and select ‘Home’, ‘Format as Table’ and choose a format you like. It’s because by making it a ‘Table’, PowerApps can clearly recognize the source data range.
Another requirement is, when you use formulas in the data, you have to change it to ‘Value’ before saving the file on the cloud. Unfortunately PowerApps doesn’t recognize the value of formulas. Simply select and copy the range of the formulas, right click and select ‘Values’ in the ‘Paste Options’.
Four Important Tips for PowerApps’ Source Data in Excel
Next let’s talk about the 4 tips. The 1st tip is to clarify the data format of each column which will prevent problems later. Select a column, and you can change its format by selecting ‘Home’ and ‘Number format’.
Especially don’t leave any data as ‘General’. After exporting the data to PowerApps, there are times the data changes to an unexpected format. Please clarify each column’s data whether it’s ‘Text’, ‘Percentage’, ‘Date’, ‘Currency’ or ‘Number’.
The 2nd tip is that all structural names used by the software should be only alphabet’s letters and underscores. You should avoid using special characters. It’s better even to use an underscore (‘_’) instead of a space, which prevent garbled characters. I had an unknown error that occurred, but it was because there was a period (‘.’) in one of the column headers.
The 3rd tip is that since these names will be referred many times in PowerApps’ work, it’s better to name each data specifically rather than ‘Table_1’ or ‘Header_2’. For example, since this is a table of transactions, I set it to ‘TBL_Transaction’ and since this is a transaction day, I set this header ‘Post_Date’. Later this will make your job very easy.
The last tip is, if you have multiple tables in one file, you should put each table in a different worksheet. When updating that table from PowerApps, the column sometimes shifts and an error will occur.
Then, save your source data in the cloud. I used Google Drive, but PowerApps is compatible with all major cloud services. It’s very useful.
Since PowerApps is a relatively new technology, it’s a problem that there is little information out there. At the beginning I did not know about the source data tips that I talked about today. I tried over and over again and spent a lot of time. I don’t want you to do the same, so I’ll share more of what I learned about PowerApps from now on.
