Learning with Mr. Dang | Transferring Knowledge from Excel (Part 1)

Brian Dang, Senior Program Manager, Microsoft Power Platform, Sunday, 1 July 2018

Do you remember learning a second language in high school? You'd conjugate verbs, decline nouns, and translate sentences. Even though the language was new, you didn't learn in a vacuum–you had a primary language for comparison. So when you learned about sentence structure, you'd connect similarities from your first language to your second. If you encountered an unfamiliar form, you could recall root words from Latin, Greek, or another parent language to derive its meaning. You took what you knew from one language and applied it to learn another:

You transferred knowledge.

If you come to PowerApps from using Microsoft Excel, you bring with you a significant body of knowledge about formulas and table structure that can transfer. Don't let your skills sit idle. You can take an existing spreadsheet-making skill and augment it into an app-building skill. But how do you actually carry it over?

The Formula Reference

If you want to learn something new, look to those who already have. There is at least one thing in common between every member from our community that I've met: they had learned how to build canvas apps using the Formula Reference page. And so did I!

So bookmark this page: Formula Reference

(Bonus: you can now enjoy it in the dark theme)

The reference page is an index of all the functions in PowerApps. Many of the functions will look familiar to an Excel user: If(), Sum(), and Concatenate() to name a few. Like the other docs from Microsoft, each page contains a definition of the function, its syntax, and samples of how it is used.

The reference on its own is not a teacher though. You don't learn a second language by reading a dictionary from A to Z. Below are concrete steps you can take to transfer your knowledge from Excel to PowerApps and learn the functions in a new context:

Start a checklist.
Take a quick inventory of functions you recognize.
Build apps and return to the formula reference as needed.
Search external documentation.
Revisit the essentials from Excel: If(), LookUp(), and concatenation.

Since transfer is an involved process, this first part will explore steps 1 and 2. Understand that getting better at using formulas is only one of the many ways to grow as an app maker, not a requirement. Read on to find out how you can carry over this existing skill set.

Start a checklist

A checklist allows you to track the progress of what functions you know, and more importantly, what functions you don't know. When you 'know what you don't know,' you can set goals on what to learn next and when. From the start, you might think of copy pasting the page into OneNote for its excellent checkbox experience.

The list works, but let's improve upon it. The alphabetical sorting is useful for finding a function on the page, but learning is a process of organizing and connecting content. Let's group the functions by which category they belong to so that we build more relationships within the content we are learning. If you click the fx icon beside the formula bar in PowerApps, you can view predefined categories and which functions fall below them.

To add in the category metadata, copy the same formula reference list into an Excel spreadsheet–you didn't think we were only going to be working in PowerApps did you? Insert a column where you type in the categories as they're grouped in PowerApps or type in your own categories for more personalized learning.

You can filter the rows by category and copy each group into OneNote so you can get a nifty checkbox again. Your checklists are ready.

Learning is a process and I encourage you to make this checklist yourself as an exercise and to get a first look at all the functions. I'm also sharing my checklist (Excel | OneNote), so you can skip to the next section. Note that as new functions come along, you'll need to update your checklist for your own records.

Take an inventory

In this step, you'll quickly glance at your checklist to check off all the functions that are familiar to you. You can do this alphabetically among the categories, but let's be purposeful again. If you view my checklist, you'll see that I've applied yet another layer of organization to it so that each category of functions is placed in a strategic location.

Each row signifies a level of transferability–how faithfully does a function transfer from Excel to PowerApps:

Top row: text, math, datetime, logical
Middle row: color, information, action
Bottom row: table, data*

*Note that I added 'data' as one of my own categories.

The top row of functions should transfer 1:1 since spreadsheets display text, numbers, and dates.

If there is any difference between the platforms, it's simply that you'll just need to learn how to reference a control, a field, or a table instead of a cell/array.
Some of these functions have improvements on their Excel counterparts. If() no longer needs to be nested and can list conditions and outcomes in a series.
Although you might not use all of the math functions like Sin() and Cos(), you can check them off your list as well as they work as you would expect.
You should click into the functions you use often as you check them off during this step. There are some subtle differences in syntax, but they may follow a common pattern.

The middle row includes functions that are basic to running an app and are not found in Excel, but whose ability you can easily infer.

You can probably guess that Back() returns the user to the previous screen and that Exit() closes the app.
The functions for color are straightforward and RGBA simply separates channels for red, green, blue, and opacity.
A few of these functions are common to both platforms like IsBlank(), or they transfer based on their pattern. IsNumeric() and IsToday() follow a common pattern for conditions like ISNUMBER and ISTEXT are used in Excel.
The pages for these functions may be the first you visit when you start building apps.

The last row includes functions related to shaping tables and working with connected data. Both of these categories are mostly native to PowerApps, but understanding table structure in Excel will help in visualizing what each function does.

You will recognize Count, CountA, CountIf, and CountRows, which would normally take a range of cells as an array in Excel. But in PowerApps, you would use the name of a table or a filter on the table as an array to count.
Some functions will transfer for the experience they deliver. AddColumns() fulfills the functionality of a 'helper column,' but it is not a permanent column like it would be in Excel.
If you're coming from Google Sheets, you'll be familiar with Filter() and a few other functions in the table category.
It is okay if you do not check off any functions from these categories. Since they are the ones that add significant value on top of what you could normally do in a spreadsheet, they are naturally going to be new to you.

Check-in

Let's review some big ideas about learning in this section:

there's more to learning than just reading from A-Z
learning is a process of organizing and connecting content: we categorized each function
content can be organized even further: we sorted the categories by transferability

Now you have an overview of all the functions. You know what you can transfer, and you know what you don't know. In the next part of this series, I'll describe in greater detail what the actual actions of transfer look like.