A formula in Excel can total economy in seconds, and now imagine that same kind of formula
deciding whether an approval button appears in an app, calculating an expense claim, or
changing what a user sees after they fill in a form.
That is PowerFX.
Hello everyone, and welcome to another episode of Microsoft Knowledge Nuggets here on M365.
FM, I'm Mercopeterz, and today we're talking about Microsoft PowerFX in plain English.
You may have heard the name while looking at Power Apps in thought, "Is this coding?
Do I need to learn a whole programming language before I can build anything useful?"
Not really.
PowerFX is Microsoft's formula language for the Power Platform.
It brings a familiar spreadsheet way of thinking into app building, so you can create rules
for screens, forms, lists, buttons, and business data without starting from the world of traditional
code.
Think of Power Apps like a modern office building.
The screens are the rooms, a form is a room where someone enters a request, a list is a
room where a manager reviews those requests.
A button is a door someone can press to move forward.
PowerFX is the set of small rules behind the scenes.
It controls what a light switch does, when a door should stay locked, and which sign appears
when a request needs more information.
Most users never notice those rules.
They just see an app that responds to them, and that matters because business apps are
full of rules.
A person enters hours and a rate, then the app needs to show a total.
Someone chooses a request type, then the app may need to show more fields.
A manager opens a list and needs to see only the requests waiting for approval.
Not logic, those are just boxes, labels, and buttons sitting on a screen.
PowerFX gives those parts a job.
Over the next few minutes we'll start with the Excel mindset you already know and then
move from spreadsheet cells to app properties, so you can see why a formula can control text,
color, visibility, and more.
After that we'll look at how formulas help apps respond to people and work with business
information.
But first, let's begin with the familiar part, because if you understand how a spreadsheet
formula thinks, you already have a strong starting point for PowerFX.
And block one, from spreadsheet cells to app properties, 550 words.
Most people feel nervous when they hear the word code.
But if you've ever typed a formula into Excel, you've already worked with a simple form
of logic.
You entered value somewhere, wrote a formula that used those values, and Excel returned
an answer.
Maybe it was a total, a date, or a message such as, over budget.
PowerFX uses that same basic pattern.
Here's the simplest definition.
It's Microsoft's Excel-like formula language for Power Platform tools, especially Power Apps.
It lets you tell an app what something should show when something should appear, or how
a value should change based on the information around it.
In Excel, a formula lives in a cell.
You might type something like x, b2, c2.
If b2 contains the number of hours, and c2 contains an hourly rate, the cell with the
formula shows the total pay.
Change either number, and Excel updates the answer.
That's the mental model to keep.
A Power Apps screen doesn't have a grid full of cells.
It has controls.
A control is simply something on the screen.
A text box where a person types, a label that displays information, a button, a drop-down
list, an image, or a gallery that shows a list of records.
Each control has properties.
A property is a setting that describes how that control looks or behaves.
A label has a text property.
A button has a text property, too, along with properties like visible and color.
The property is where PowerFX comes in.
Instead of putting a formula in cell d2, you put a formula into the text property of
a label.
Imagine you're building a simple time sheet app.
You add one input box for hours and another for the hourly rate.
When you add a label called total pay.
Rather than typing a fixed number into that label, you give its text property a formula
that uses the value from the hours input and the value from the rate input.
The label becomes your calculated cell.
A person changes the hours from 5 to 8 and the total changes.
They change the rate and the total changes again.
Nobody needs to press a calculate button just to refresh the label.
The formula keeps the label tied to the values it depends on.
Much like Excel keeps one cell tied to another.
That automatic response is a big part of how PowerFX feels.
You describe the result you want and Power Apps keeps the result up to date while the person
uses the app.
There's one change that catches many Excel users at first.
In a spreadsheet, you often point to a cell address like b2 or c2.
In PowerFX, you usually point to things by name.
An input control in one of its properties?
A record from a list or a column in a table.
So the question shifts from which cell holds this number?
To which control record or column holds this information?
That sounds small, but it changes how you look at the screen.
You stop seeing a screen as a picture made from separate pieces and start seeing it as
a set of connected values.
The hours input provides one value.
The rate input provides another.
The total pay label reads both values and displays the result.
Later, the same idea can control whether a warning appears, what color an amount uses,
or what text a person sees.
For now, keep it simple.
Start with formulas that describe what a screen should show.
Choose one label, pick one value a person can enter, and then make the label respond to
it.
That first small connection is where PowerFX starts to feel less like coding and more
like building with formulas you already know.
Applications are useful, but an app also needs to respond when someone clicks something.
Building block two, properties turn formulas into app behavior.
Here's the thing.
A calculation tells you what a value should be, but an app also has to react when a person
does something.
A button without logic is just a shape with a label.
It might say submit request, but until you give it a rule, pressing it does nothing.
That's where app properties become more interesting.
Every control in Power Apps has settings that PowerFX can work with.
Some describe what the control looks like right now, while others run an action after something
happens, two different jobs.
The first job is ongoing.
A formula keeps describing the current state of a control.
Take a label that shows a total.
Its text property keeps calculating as a user changes numbers, or take an approval button.
Its visible property checks the request status.
If it's ready, the button appears, if not, it stays hidden.
You aren't telling the app to check once and forget it, you're telling it what the button's
visibility should be whenever the screen updates.
For example, a simple rule might look like this.
If requests status are ready for approval, true, false.
In plain English, that means if the status says ready for approval, show the button.
Otherwise, hide it.
PowerFX can also change what the button says.
Maybe the text property reads send for approval, while a request is being prepared, but once
it's sent, the same button could display requests submitted or disappear entirely.
Color works the same way.
An amount turns red when it exceeds a budget, or a status label changes color when a request
needs attention.
The screen responds to the information instead of showing the same fixed design to everyone.
That's one side of PowerFX, formulas that keep a control up to date.
The other side starts when a person acts.
In Power Apps, a button has a property called on select.
Sounds technical, but it simply means what should happen when someone selects this button?
Suppose you have a simple request form.
A user fills in the details and presses a button called send request.
You might set that button on select property to notify request send.
In pressed, Power Apps shows a message on screen, giving the user immediate feedback, no
guessing whether it worked.
You can also use on select to move someone to another screen.
Imagine an expense app with a list of existing expenses.
At the top, a new expense button uses navigate new expense screen.
Press it, and Power Apps opens the screen for entering a new expense.
That's app behavior in a very direct form.
A user chooses an action and PowerFX response.
The difference is worth remembering.
A text, visible, or color formula describes what something should be, while an on select
formula runs because a person clicked or tapped.
Both sit in properties, but they solve different problems.
This is where beginners get stuck.
They learn a function like if or notify, then wonder where to put it.
Start from the outcome, not the function name.
Do you want a label to change?
Select the label and work with text.
Do you want a button to appear only in one situation?
Select the button and work with visible.
Do you want something to happen after a click?
Select the button and work with on select.
One control, one property, one clear outcome.
That's a much easier way to build.
Once an app starts reacting to a person's choices, the next question is obvious.
Where does all that information live?
Building block three.
PowerFX works with business data.
A screen can calculate a total, show a message, and move someone to another page.
But most business apps need to work with information that exists beyond that one screen.
Think about an expense app.
The expenses need to live somewhere after submission.
A manager opens the app tomorrow and sees the same requests.
Finance reviews the amount category, date, and employee name.
That's where data comes in.
Think of your data source as a filing cabinet.
Inside, tables are the drawers.
When an expense table is one drawer, an employee's table another.
Each record is a folder inside a drawer and each column is a label on that folder.
One expense record might include the employee name, date, category, amount, and approval status.
PowerFX helps your app read from that filing cabinet, find the folders it needs, and sometimes
add or update information.
Before we look at formulas, keep three words straight.
A table is a group of related records.
A record is one item in that table.
And a column is one type of information stored for every record.
So in an expenses table, one record could be a train ticket with amount 42.50, category,
travel, and status submitted.
An app doesn't always need to show every folder in the drawer.
Imagine an employee opens the expense app and wants to see only their own claims, while
a manager wants only claims waiting for approval and finance wants only travel expenses.
That's the job of filter.
A filter formula asks for a smaller set of records that match a rule.
In plain English, you might tell the app.
Show expenses where the employee name matches the current user or show only records where
category equals travel.
The result appears in a gallery.
The power apps control the display is a repeating list.
Instead of building separate screens for every category, you give one gallery a formula
that responds to the user's choice.
Choose travel and the gallery shows travel.
Choose equipment and the list changes.
The records in the filing cabinet haven't disappeared.
The app simply selected the folders the person needs right now.
What if the list is correct but in the wrong order?
That's where sort comes in.
A busy manager wants the newest requests at the top, while finance needs the highest amounts
first.
Sort lets you arrange records before displaying them.
Filter answers which records should I see?
Sort answers in what order?
Often you use both together, for example filter for pending approval requests, then sort
by date so the newest appear first.
There's another common need.
Sometimes you don't need a whole list.
You need one matching record.
Maybe an expense claim needs to check an employee's approval limit.
The app looks in an employee's table, finds that person's record and reads the limit.
That's the pattern behind lookup.
Lookup finds one record that matches the rule you give it.
You can think of it as asking someone at the filing cabinet, please find the folder for
this employee.
Once you have that folder, you can read the information inside it.
Reading data is only half the job.
Apps also collect information.
A person enters a new expense, a manager changes the status to approve, someone corrects
the category, power effects can add or update those records.
Still, this is where you need to slow down.
The app may show a button, but the data source controls who can read, add or change information.
An expense app shouldn't give every user the same access just because they can open a screen.
Your data permissions matter.
Power apps can connect to places where many companies already keep information, SharePoint,
Excel, SQL, Dataverse.
Those sources don't all work the same way, and the right choice depends on your app's
needs.
But for a beginner, the point is simpler.
Your formulas and your data structure belong in the same conversation.
If the table has unclear columns, the formula becomes harder to write.
If the formula asks unclear questions, the app returns confusing results.
Name your columns clearly, know which record you need, then write the formula that finds,
filters, sorts, or updates that information.
Reading and changing records handles a lot of app work, but rules can become difficult
to manage when the same answer needs to appear in more than one place.
Building block 4.
Variables, collections, and reusable rules.
So once your app has more than one screen, it needs a way to remember small things while
someone moves around.
Imagine someone selects an expense from a list.
They open a detail screen, but that second screen needs to know which expense they selected,
even though the list sits on a different screen.
That's where a variable comes in.
Think of a variable as a labeled box inside the app.
You put a value in, give it a clear name, and then use that value somewhere else while
the app runs.
The value could be a selected expense of persons chosen category, or a running total for items
they've added to a request.
For instance, a user selects an expense in a gallery.
The app saves that record in a variable and moves to a detail screen.
On that next screen, the labels read the selected expense from the variable and show the
amount, date, category, and status.
The user sees one smooth journey, but behind the scenes the app remembered their choice.
Now variables are helpful, but they aren't permanent storage.
If you need an expense record to be available tomorrow for finance or a manager, it belongs
in your connected data source.
A variable only helps the app keep track of something while the session is running.
Knowing the difference saves a lot of confusion.
One box holds one value, but sometimes your app needs to remember a small list of values.
That's where collections come in.
A collection is like a temporary table inside the app.
It can hold several records while the user works through a task.
A simple shopping cart is an easy example.
A person selects three items from a product list.
The app adds them to a collection and the cart screen shows the selected items, calculates
the total, and lets the user remove one before submitting the order.
You could use the same idea for a safety checklist.
A field worker marks several checks as complete and the app keeps those choices in a collection
while they work through the form.
When they finish, the app saves the completed result to the proper data source.
Customs are useful for short term work, but they aren't a replacement for the source where
your business records belong.
That matters when you're building something people will rely on.
If a collection holds the only copy of an important request, you've created a problem,
the user might close the app, lose their connection, or start again later, and that temporary
list might be gone.
So, use collections when the app needs a working list and use your data source when the business
needs a lasting record.
As your app grows another challenge appears, the same business rule starts showing up in
more than one place.
Maybe an order receives free delivery only when the total reaches a certain amount and
a discount code is still valid.
You might need that answer on the checkout screen, the order review screen, and later in an
automated process.
Copying the same long formula into each place feels quick at first, but then the delivery
rule changes.
Someone updates one copy and misses the other two, and now different parts of the same
solution give different answers.
Reusable PowerFX functions help you avoid that mess.
You define a function once, give it the inputs it needs, and it returns an answer.
For the delivery example, the function receives an order subtotal and checks whether the
discount code meets the date rule.
Its result is either true or false.
True means free delivery applies, false means it doesn't.
The app doesn't need to carry the entire rule everywhere.
It can ask the shared function for the answer.
That makes the rule easier to check, easier to change, and easier to keep consistent.
PowerFX appears most clearly in canvas apps where you work directly with controls in the
formula bar, but the same formula language also appears in data versus other power platform
experiences.
You don't need to master every place it appears before you start.
Just understand the direction Microsoft is taking because the same way of writing business
rules can travel beyond one screen in one app.
For you, the practical lesson is simple.
When a value only matters during a person's journey through the app, use a variable or a
collection with a clear purpose.
When the same business rule needs to return the same answer in more than one place, keep
that rule together instead of copying it.
All of these pieces can sound separate when you first meet them, but they make more
sense when you watch them work together in one small app.
While the parts connect, one formula system.
Let's walk through a simple expense approval app from start to finish.
An employee opens the app and starts a new expense claim.
They enter an amount, choose a category, add a date, and write a short description.
Each input control now contains a value.
PowerFX can use those values straight away.
A label can calculate a total, a formula can check whether the amount is above the employee's
normal limit, and another formula can show a message if the description is blank.
The screen changes as the employee fills in the form, then they press submit.
That button's action formula saves the request and moves the person to a confirmation screen.
The app stores the newly selected record in a variable so the confirmation screen knows
exactly which claim to display.
At the same time, the app writes the lasting information to the connected data source.
Later, a manager opens their approval screen.
The gallery doesn't need to show every expense in the company.
The formula can return only the records waiting for that manager, and another formula can
arrange those records by date or amount depending on what the team needs.
The manager selects one record.
The detail screen reads that choice shows the full request and checks the relevant approval
rule.
If the amount sits within the limit, the approval action appears.
If the request needs more detail, the app shows a different message or sends the manager
back to the employee for more information.
One business rule may also be shared.
A delivery rule, spending rule, or discount rule can live in a reusable function so the app
doesn't create a new version of the answer on every screen.
This is the part that usually clicks.
PowerFX doesn't sit next to your app like a separate pile of code.
It supplies the rules inside the app itself.
The controls use formulas to display the right information, buttons use formulas to respond
to people, and data formulas select and update the records the app needs.
Variables and collections hold short term context, while shared functions keep repeated
rules consistent.
Older app projects often split these jobs across separate scripts, hand-built pages, and
custom logic written far away from the screen where people use it.
PowerFX keeps much of the logic close to the control, screen, or data, step-it-effects.
It can make a small business app easier to read, especially when you name things clearly.
Still, PowerFX isn't a promise that every app problems days simple, large data needs,
strict security rules, complex database changes, and work that connects many systems may
need help from experienced developers, data teams, or other power platform tools.
That's not a failure of low-code, it's simply choosing the right tool for the job.
For your first practice session, don't try to build the entire expense system.
Build one small screen, then give it one useful rule.
Here's a simple approach.
Grab one blank canvas app and one safe idea, like a simple request form.
First, make one label respond to an input, then add an if rule that changes what the user
sees, and finally give a button in action with notify or navigate.
Don't try to memorize every function.
Instead, use the formula bar suggestions and read the hints for each input the function
needs.
Clear names also help, so name controls, screens, variables, and data columns, so you can
tell what they do later.
Build one small screen and test it after every change.
And remember, you don't need to build everything at once, just take it one step at a time.
PowerFex brings that spreadsheet mindset into app building.
It covers everything from what users see to what buttons do and how records move.
So here's the key.
Start with one property and one formula, and that's where AppLogic stops feeling mysterious.
If you want more Microsoft, knowledge nuggets in plain English, subscribe to M365FM.
And share this episode with someone moving from Excel into PowerApps.