Intro to PowerFx For T-SQL Developers
I was working on an app that required a new screen. I was asked to take a screen that had 2 nested galleries and then go a third level down. Seems simple, right?
After going back to start development, I remembered that you can only go one nesting level down. In other words, you can only nest a gallery inside another. You cannot nest inside a nested gallery.
So, what to do? A coworker suggested using CONCAT() for the third level. At first, I thought maybe that would not work, because the third level had 4 different data sources that needed to be expanded. But then I thought, “Why do I need any nested galleries if this is read-only?”
The ultimate solution required some coding and some thinking outside of the box. I approached the problem as if I was writing T-SQL and used collections and it made it quite easier, so I thought I would write something up about how you could mash up data using PowerFx if you are new to it but know T-SQL.
SELECT with ShowColumns(), DropColumns()
The SQL SELECT statement is one of the basic foundational statements of querying with SQL. It answers the question: What columns do I want to return? You could select all (*) or if you want to only ask for the columns you need, you can specify by name.
Dataverse tables, just like SharePoint lists, have many columns in the table that you would never really need for anything in an app. In the app I was working on this week, I was pulling things into a collection (more on that later) and wanted to see intermediate results because I was using multiple collections in tandem. Those extra columns started being a pain because I had to scroll left-to-right more to see what columns I actually needed. So I used ShowColumns().
ShowColumns() is simply what columns you want to see from the data source. Here is an example:
ShowColumns(DataSource, "Column1", "Column2", "Column3")
You could also use DropColumns() to just exclude the columns you do not need:
DropColumns(DataSource, "ColumnToDrop")
These functions help with only pulling in what you need. It was very helpful for me as I chained collections and tried to look things up. Not having that extra noise (matadata columns I was never going to use) made my job easier.
JOIN with AddColumns()
Ok, well then how do you join? There are probably multiple ways to do this, but the easiest is with AddColumns() if you are using Dataverse. If the table you are working with shares a relationship with the other table you are looking up, you can just add columns from that table with dot notation and AddColumns(). This performs a lookup to the other table and brings the target column in, easy peasy.
AddColumns(
DataSource,
"NewColumnName",
Expression
)
If you are unfamiliar with this all, here is a great video from Shane Young at PowerApps911:
Alias (AS) with RenameColumns()
When you use AddColumns(), PowerApps will reference the added column by the schema name, which may be confusing or annoying. If you want to change this, you could use RenameColumns() (wrapped around AddColumns()) to fix that.
Another use for this is to simply alias verbose column names so that you can reference them faster, like taking spaces out and shortening the name.
Here is the syntax:
RenameColumns(
DataSource,
"OldColumnName", "NewColumnName"
)
Query with Collect(), ClearCollect(), Filter()
So if you want to query the data and get a subset or even store it, you would want to start with Collect(), ClearCollect(), and Filter().
Collect() adds records to a Collection. You can use the Clear() function to conditionally empty the collection as you see fit. ClearCollect() is a combination of the two, so when you invoke it you are emptying the collection then filling it.
Filter() can be used with the collection functions or not. Often you will see this use in the Items property of a gallery to filter a data source. You can also use Filter() in tandem with collection functions and create collections that are based on subsets of the data source. Filter() takes one filter condition, but many additional optional filter conditions, meaning you can add multiple conditions should you need to.
// Collect() function adds records to a data source
Collect(
DataSource,
{ Column1: "Value1", Column2: "Value2" }
)
// ClearCollect() function clears and then adds records to a data source
ClearCollect(
NewCollection,
{ Column1: "Value1", Column2: "Value2" }
)
// Filter() function filters records in a data source based on a condition
Filter(
DataSource,
Column1 = "Value1"
)
Other Similar Functions/Operators
Coalesce()
Coalesce() is one of those functions that are so handy. In Power Query using the ?? operator was one of the favorite things I used over and over again. In T-SQL, you obviously have this function and in Power Fx it operates the exact same way:
// Coalesce() function returns the first non-blank value in a list of arguments
Coalesce(
BlankField,
"Default Value"
)
STRING_AGG() with Concat()
STRING_AGG() in T-SQL concatenates expressions with a separator. In DAX, this is akin to CONCATENATEX(), in Excel this is like TEXTJOIN(). I know all of this because I have had to do this in each 😂.
In PowerFx, you can just use Concat(). Here is the syntax:
// Concat() function concatenates a data source into a string
Concat(
DataSource,
ColumnName & ", "
)
Concat() ended up being the special sauce as to how I could collapse the ask of 3 nested galleries to just one. If everything needed to be read-only, then using Concat() on the lowest child (3rd level down) ended up being the way to go for me. It required some HTML coding and collection trickery, but it ended up being really performant and with a flexible height gallery it looked good.
“in” Operator
The in operator is another handy thing in SQL. In Power Fx, you can do the same thing. In my case, I had four different child data sources to a parent. So what I did was grab the GUID from the parent, used ShowColumns() to create a one-column collection of parent GUIDs and that allowed me to filter very easily on the children.
As long as you can get what you are looking in to be one-column, this is super-handy, just like in SQL. Syntax is even similar:
// Our data source is called 'Employees' and it has a column 'FullName' which stores the full names of employees.
// We want to check if an employee named 'John Doe' exists in our data source.
If(
"John Doe" in Employees.FullName,
"Employee exists in the data source.",
"Employee does not exist in the data source."
)
Conclusion
For about 2 years, I wasn’t doing heavy Power Apps development. My Power Apps development previously was also limited to mostly SharePoint data sources. While I was modeling my SharePoint sources as if it was a relational database (storing IDs as foreign keys as an example), coming back to Power Apps with Dataverse was remarkably different. But, having spent the better part of 2 years doing heavy data analytics with T-SQL, Power BI, and Python, helped me transition easier.
Why? Getting a lot of practice with relational data and querying. Having more and more experience helped me visualize in my mind what I needed as a result. If you can do that, then you can incrementally build the pieces to get what you need.
In many situations transitioning back and forth between different coding languages/technologies involves change management mostly. What I mean by that is that if you have already been competently coding for a while, you already have the skills to code in another language. Most of the time it is just learning how to do it in that language. A lot of my growing pains have not been from a lack of understanding of the concept, but a lack of understanding as to how to apply that concept in the language. If you look at the problem that way, you could see that it really just takes practice, time and experience.
So if you have been happy coding in T-SQL and get thrown a Power Apps project out of the blue, you can do it. You are capable. You may get frustrated with the syntax at times and may not understand something, but just remember to take a step back and remember that we have all been there.
Comments are closed.