With(), Let, & VAR: The Three Amigos of Power Platform Efficiency

I started a new job recently as a Power Platform Developer and it has been fun doing more work with Power Automate and Power Apps. Previously, I had spent 2 years doing more data analytics work with Python and Power BI (both Report Server and Service implementations). I realized I missed a lot of cool things that were introduced in Power Apps (like there’s a Copy() function now!?!?), so I have been trying to get acclimated with the changes.

After working in Power BI primarily and coming back to the broader Power Platform, I noticed similarities in frameworks that make you more efficient in DAX, Power Query, and PowerFx. When you master these three areas, things are simpler to work with. Also, you get better in other areas.

For example, if you master variables in DAX, then learning the general framework of M is easier. If you master these two, then the With() function in PowerFx is intuitive.

I will attempt to go over all three as I understand them. I am not trying to write very technical and am going off the top of my head. The goal is really to look at each of these broadly so that you can get a general sense of how these work, how they are similar, and how you can start using them.

VAR in DAX

Variables in DAX are defined using the VAR keyword. They are very easy to use and they help you debug faster and break complicated queries into steps, rather than trying to solve all of the world’s analytics problems in one massive nested mess.

The general setup is something like:

MyVeryCoolMeasure =

VAR _firstStep = COUNTROWS(...)
VAR _secondStep = MAXX(...)
VAR _result = SUMX(.....)

RETURN

_result

In this very simple example of how these are structured, you can add individual calculation steps by declaring a variable with VAR (note, I picked three random functions for instructional purposes only). Major benefits of using variables are (not inclusive):

• I can break up complicated calculations to distinct steps
• I can debug each step by switching the return to another step in the process
• I can better document each step with comments that clearly leave a breadcrumb trail
• This is easier for someone else to follow or even myself 6 months down the road

There are other reasons to use variables, but for now this is enough for me to use them (and you should too if you aren’t yet).

Let and Power Query

Power Query transformations mostly follow a pattern that resembles this:

let
     Source = Sql.Database(....),
     first_step = Table.TransformColumnTypes(...),
     second_step = Table.TranformColumns(...)
in 
    second_step

Again, I picked three functions randomly (the functions used are not important).

Transformations tend to start with let and then after a series of steps are added until all of the transformations are done. Each step ends with a comma except for the final transformation step. After the last one, the keyword in is added and usually the last step is referenced to close out the query.

While you generally reference the last step after the in keyword, you ultimately don’t have to. Why would this be helpful? Because you can debug each query step just by changing the step referenced after the in keyword. This is similar to debugging with DAX.

Additionally, looking at this framework in DAX more broadly, you can think about each step in M as almost like a variable in DAX in the sense that each step is a declared piece of the whole puzzle. Breaking up things into distinct steps, being efficient with what you do, and documenting along the way can help you in either M or DAX.

Adding With() and PowerFx to the Mix

With(), when I first saw it, was not 100% entirely intuitive to me. I mean, I could code a function fine. I just did not entirely recognize use cases in my day-to-day that I was ready to apply it to.

Coming back to Power Apps, I now LOVE this function. I cut a lot of what would otherwise be very verbose code into a couple of lines. It really shaves off a lot of what I have to write.

The general setup of With() works something like this:

With(
   {
      variable_one: 1,
      variable_two: 2,
      variable_three: 3,
   },
      variable_one + variable_2 + variable_3
)

You would get the value 6 if you set this to a variable. Again, you can define a series of variables upfront (in curly brackets) and then afterwards use them to perform some sort of calculation that you can return. You can even nest With() functions, which allows you to really do some cool stuff!

Comparing to DAX and Power Query (M), you can see similarities clearly. Define variables or steps upfront that will help you return something you need to return.

Conclusion

VAR, let, and With() are three areas of Power BI and Power Apps that allow you to be more efficient in development. They are also quicker to learn if you master one area (at least to me). They each have different syntax, but the very general way to set these up is similar. Expanding to even Excel (with LET and LAMBDA) and you can probably find even more similarities and streamline even more of your day-to-day.