{"id":2090,"date":"2015-09-21T00:00:00","date_gmt":"2015-09-18T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/power-query-formula-language-in-power-bi-desktop\/"},"modified":"2021-05-17T18:33:14","modified_gmt":"2021-05-17T18:33:14","slug":"power-query-formula-language-in-power-bi-desktop","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/power-query-formula-language-in-power-bi-desktop\/","title":{"rendered":"Power Query Formula Language in Power BI Desktop"},"content":{"rendered":"
\n

Power BI Desktop provides a robust adjunct to the Power BI services by offering access to a wider range of data sources and transformations, in addition to its offline capabilities. At the heart of Power BI Desktop is the Power Query Formula Language (PQFL), a dynamically typed, case-sensitive functional language for creating mashup queries, similar to the F# language. Although Power BI Desktop has a number of UI features for simplifying transformations, PQFL underpins those transformations, and the better you understand the language, the more you can take advantage of the capabilities inherent in Power BI Desktop.<\/p>\n

Formerly known as M, PQFL has its roots in Power Query for Excel, and as such, much of the information you find about the language is specific to Excel. Although this will likely change as Power BI Desktop catches on, currently it can be difficult to find examples specific to Power BI Desktop that will help you resolve a particular issue. That said, PQFL follows a very specific formula that is uniform enough to make it relatively painless, at least in some cases, to come up with solutions on your own, as long as you know the basics.<\/p>\n

In this article, I introduce you to PQFL so you can see for yourself how to use the language to create mashup queries from scratch. If you’re not familiar with Power BI Desktop, I suggest you first review my article Working with SQL Server data in Power BI Desktop<\/a>, which provides details on how to get started using the application to retrieve and transform data.<\/p>\n

Each transformed data set in Power BI Desktop is essentially a PQFL query that retrieves data from a source and modifies the data in various ways. To create a PQFL query, you start with a let<\/b> expression that contains a series of expression steps.<\/p>\n

Each step is in essence its own expression that assigns a value to a variable, based on a combination of functions, operators, and value types. The value types can include simple primitive scalar values such as numbers or strings,<\/i> or include objects such as lists, records, or tables. The variable can then be used in one or more subsequent expression steps, which themselves define additional variables. This will all become clearer as we work through a few examples. <\/p>\n

For this article, we’ll be retrieving data from a SQL Server database, so we’ll be restricting ourselves primarily to table objects and primitive values; however, the concepts we cover apply to all object types. To demonstrate these concepts, I created a set of examples based on data from the AdventureWorks2014<\/b> database on a local instance of SQL Server 2014. Each example builds in the previous one in order to demonstrate how to create a PQFL query one expression step at a time. With that in mind, let’s get started.<\/p>\n

Retrieving data from SQL Server<\/h1>\n

In Power BI Desktop, you can create a PQFL query without first connecting to a data source. Instead, you define the connection within the \tlet<\/b> expression, as the first expression step. To begin, open Query Editor and take the following steps:<\/p>\n

    \n
  1. Click the New Source<\/b> button on the \tHome<\/b> ribbon, and then click Blank Query<\/b>. A new query is added to the Queries<\/b> pane (named Query1<\/b>, by default).<\/li>\n
  2. In the Properties<\/b> section of the \tQuery Settings<\/b> pane, type a new name for the query. I used \tRepSales<\/b> because we’re going to create a data set based on the annual sales of the Adventure Works sales reps.<\/li>\n
  3. In the Queries<\/b> pane, right-click the new query, and then click \tAdvanced Editor<\/b>.<\/li>\n<\/ol>\n

    The last step launches the advanced editor, with the start of a \tlet<\/b> expression already defined, as shown in the following figure.<\/p>\n

    \t \t\"2285-img3C.jpg\"<\/p>\n

    The \tlet<\/b> expression includes two parts, the let<\/b> statement and the \tin<\/b> statement. The \tlet<\/b> statement defines the expression steps, and the \tin<\/b> statement specifies variable, which determines what data the query returns.<\/p>\n

    The first expression step in the let<\/b> statement defines the Source<\/b> variable as an empty string. Ultimately, the variable will be used to identify the initial data source. <\/p>\n

    You don’t need to stick with naming the variable Source<\/b>. You can use just about any name you want within reason. PQFL even supports variable names that contain spaces, but then you must enclose the variable name in quotes and precede it with a hash mark, a rather messy coding convention that I try to avoid. Whatever you name your variables, keep in mind that PQFL is a case-sensitive language, which applies to all elements.<\/p>\n

    The first step, then, is to change the variable definition to something other than an empty string. For that, we can turn to one of the many PQFL functions available to the various types of objects and primitive values. Because we will be retrieving data from a SQL Server database, we’ll use the \tSql.Database<\/b> function to specify the SQL Server instance, database, and a T-SQL query, as shown in the following \tlet<\/b> expression:<\/p>\n

    \tlet\n\t  Source = Sql.Database(\"localhost\\sqlsrv2014\", \"adventureworks2014\", \n\t    [Query=\"SELECT BusinessEntityID, FirstName, MiddleName, LastName, \n\t      JobTitle, City, StateProvinceName, CountryRegionName, TerritoryName, \n\t      TerritoryGroup, SalesYTD, SalesLastYear\n\t      FROM Sales.vSalesPerson;\"])\n\tin\n\t  Source\n\t \n<\/pre>\n
    As you can  see, the Source<\/b> definition uses the \tSql.Database<\/b> function to call the \tAdventureWorks2014<\/b> database on a  named SQL Server instance (localhost\\sqlsrv2014<\/b>).  The Query<\/b> option, which is enclosed  in brackets, defines a straightforward T-SQL query that retrieves data from the \tSales.vSalesPerson<\/b> view. Of course,  we can make our T-SQL query far more complex, or we can keep it simple and do  the work in our let<\/b> expression, which  is what we’ll do here.<\/p>\n
    That’s all  there is to defining the initial data source. The following figure show what the \tAdvanced Editor<\/b> window should look  like after we’ve updated the Source<\/b>  definition.<\/p>\n
     \t \t\"2285-img3D.jpg\"<\/p>\n
    Before we  continue, it’s worth pointing out that the advanced editor in Power BI Desktop  is anything but advanced<\/i>. It is barely  a step above Notepad, providing only a rudimentary syntax checker. If it does  find a problem, the checker often fails to point to the correct part of the  syntax, or worse still, the checker misses the error altogether, and it’s not  until you run your query that you discover something is wrong. <\/p>\n
    You also  cannot save your changes as you go along within the advanced editor. If the  syntax checker fails to find an error, and running the code crashes the system,  you will lose all your changes, which has happened to me. One way to protect  yourself again this particular scenario is to copy your work over to Notepad or  some other text editor as you’re working so you don’t risk losing what you’re  doing. You should also run your query whenever you add or modify an expression  step. If the query runs successfully, you can apply and save your work.<\/p>\n
    The advanced  editor is not particularly conducive to trial-and-error development. Whenever  you want to check the results of a code change, you must exit the environment to  run the query (and save it) and then re-launch the editor to continue, usually  having to resize the window with each cycle. With luck, Microsoft will improve  this feature at some point in the future, if enough of us whine about it.<\/p>\n
    Now that  you’ve been warned, we can move forward. If you’re satisfied with your \tlet<\/b> expression as it stands, click \tDone<\/b> to close the advanced editor and  run the query. Your results should now look similar to the Query Editor window  shown in the following figure.<\/p>\n
     \t \t\"2285-img3E.jpg\"<\/p>\n
    If you’ve  gotten this far, you have now successfully retrieved the data from SQL Server  and you can start transforming it.<\/p>\n
    Filtering rows<\/h1>\n
    Often, when  you’re developing your let<\/b>  expression, you will use the variable you define in one step as part of the  expression in the next step. In this way, you create your query as well defined  and delineated steps that build on each other.<\/p>\n
    To  demonstrate how this works, we’ll filter out all rows whose \tTerritoryName<\/b> value is null. The idea  is to remove all managers from our list so we end up with only the sales reps.  We can instead filter the data based on the \tJobTitle<\/b> column, retrieving only those rows that contain the \tSales Representative<\/b> value. I went  with the nullability factor because strings are easier to mess up when you’re  typing, but you can do whatever works best in your particular circumstances.  Given that this is just an example, I went for the low-hanging fruit.<\/p>\n
    To filter  the data, we’ll use the Table.SelectRows<\/b>  function. As you might have noticed with the \tSql.Database<\/b> function, PQFL functions are usually specified as  two-part names. The first part is the object type, and the second is the actual  function name. For the Sql.Database<\/b>  function, the object type is Sql<\/b>, and  for the Table.SelectRows<\/b> function,  the object type is Table<\/b>.<\/p>\n
    The \tTable<\/b> object type supports a wide  range of functions, as do all the object types. You can find a comprehensive  list of the PQFL functions in the \tPower Query  Formula Reference<\/a>. In the meantime, let’s return to our \tlet<\/b> expression and add a second  variable definition, which we’ll name SalesRep<\/b>:<\/p>\n
    \tlet\n\t  Source = Sql.Database(\"localhost\\sqlsrv2014\", \"adventureworks2014\", \n\t    [Query=\"SELECT BusinessEntityID, FirstName, MiddleName, LastName, \n\t      JobTitle, City, StateProvinceName, CountryRegionName, TerritoryName, \n\t      TerritoryGroup, SalesYTD, SalesLastYear\n\t      FROM Sales.vSalesPerson;\"]),\n\t  SalesRep = Table.SelectRows(Source, each([TerritoryName] <> null))\n\tin\n\t  SalesRep\n\t<\/pre>\n
    To add a new  variable definition, we add a comma after the first expression step and start  the new definition with the variable name, as we did with the first one. There  is no need to specifically declare or type variables in PQFL. The typing occurs  as part of the variable definition. <\/p>\n
    In this  case, we’re creating a table object, as determined by the the \tTable.SelectRows<\/b> function, which returns a table object, just like  the Sql.Database<\/b> function. The \tTable.SelectRows<\/b> function takes two  arguments. The first is the input table, the \tSource<\/b> variable, and the second is the expression that determines  which rows to filter out. In this case, the expression uses the \teach<\/b> function to iterate through the rows. For each row, the \tTerritoryName<\/b> column must not be  null.<\/p>\n
    That’s all  there is to filtering rows, but also notice that the \tin<\/b> statement now calls the SalesRep<\/b> variable, rather than  \tSource<\/b>.  The SalesRep<\/b> variable contains the  transformed data, which is the data we want to return. Generally, you should  define your if<\/b> statement to call the  last variable defined in the let<\/b>  statement.<\/p>\n
    Creating a custom column<\/h1>\n
    Next we’ll  create a custom column named FullName<\/b>  that concatenates each sales rep name. However, we’ll need to include  conditional logic in our variable definition to account for those reps with a  null MiddleName<\/b> value. For that,  we’ll include an if<\/b> expression in the  variable definition, as shown in the following \tlet<\/b> statement:<\/p>\n
    \tlet\n\t  Source = Sql.Database(\"localhost\\sqlsrv2014\", \"adventureworks2014\", \n\t    [Query=\"SELECT BusinessEntityID, FirstName, MiddleName, LastName, \n\t      JobTitle, City, StateProvinceName, CountryRegionName, TerritoryName, \n\t      TerritoryGroup, SalesYTD, SalesLastYear\n\t      FROM Sales.vSalesPerson;\"]),\n\t  SalesRep = Table.SelectRows(Source, each([TerritoryName] <> null)),\n\t  RepName = Table.AddColumn(SalesRep, \"FullName\", each \n\t    if [MiddleName] <> null \n\t    then [FirstName]  & \" \" &  [MiddleName]  & \" \" & [LastName]\n\t    else [FirstName] & \" \" & [LastName])\n\tin\n\t  RepName\n\t \n<\/pre>\n
    We start by  adding the RepName<\/b> variable,  following by the definition. The definition uses the the \tTable.AddColumn<\/b> function, which takes three arguments. The first  argument specifies the table source, SalesRep<\/b>, and the second argument specifies the name of the new column, \tFullName<\/b>. <\/p>\n
    The third  argument is the column expression that does all the work. The expression begins  with the each<\/b> function, a special  function used to iterate through a collection of data. This is followed an the \tif<\/b> expression, which defines the  conditional logic needed to know how to concatenate the names. If the \tMiddleName<\/b> value is not null, the \tFirstName<\/b>, \tMiddleName<\/b>, and LastName<\/b>  values are concatenated, with spaces in between. Otherwise, only the \tFirstName<\/b> and \tLastName<\/b> values are concatenated, again, with a space.<\/p>\n
    Although  this is only a simple example of using an if<\/b> expression, it demonstrates what a powerful tool you have available for  transforming your data. You can create a data set that accounts for different  types of values. Notice also that this time our \tin <\/b>statement calls the RepName<\/b> variable to ensure that our  \tlet<\/b> expression returns the fully transformed data.<\/p>\n
    Removing columns<\/h1>\n
    Now let’s do  a few more transformations, starting with removing the columns we no longer  need: FirstName<\/b>, \tMiddleName<\/b>, \tLastName<\/b>, and JobTitle<\/b>.  Because the expression steps build on each other, we can remove these columns  without affecting previous operations. In other words, the most recent variable, \tRepName<\/b>, represents our data set’s  current reality, allowing us to act on the variable however we need to without  looking back.<\/p>\n
    To remove  the columns, we’ll create a variable named \tRemoveCols<\/b> and invoke the \tTable.RemoveColumns<\/b> function, as  shown in the following example:<\/p>\n
    \tlet\n\t  Source = Sql.Database(\"localhost\\sqlsrv2014\", \"adventureworks2014\", \n\t    [Query=\"SELECT BusinessEntityID, FirstName, MiddleName, LastName, \n\t      JobTitle, City, StateProvinceName, CountryRegionName, TerritoryName, \n\t      TerritoryGroup, SalesYTD, SalesLastYear\n\t      FROM Sales.vSalesPerson;\"]),\n\t  SalesRep = Table.SelectRows(Source, each([TerritoryName] <> null)),\n\t  RepName = Table.AddColumn(SalesRep, \"FullName\", each \n\t    if [MiddleName] <> null \n\t    then [FirstName]  & \" \" &  [MiddleName]  & \" \" & [LastName]\n\t    else [FirstName] & \" \" & [LastName]),\n\t  RemoveCols = Table.RemoveColumns(RepName,{\"FirstName\", \"MiddleName\", \"LastName\", \"JobTitle\"})\n\tin\n\t  RemoveCols\n\t<\/pre>\n
     \tThe \tTable.RemoveColumns<\/b> function takes  only two arguments, the table source, RepName<\/b>, and the list of columns to delete. For such a list, the column  names must be enclosed in quotes and separated by commas, and the entire list  must be enclosed in curly braces.<\/p>\n
    Renaming columns<\/h1>\n
    At this  point, you should have a good sense of how the expression steps build on each  other as you continue to apply transformations to your data. We can take this  same approach to rename columns:<\/p>\n
    \tlet\n\t  Source = Sql.Database(\"localhost\\sqlsrv2014\", \"adventureworks2014\", \n\t    [Query=\"SELECT BusinessEntityID, FirstName, MiddleName, LastName, \n\t      JobTitle, City, StateProvinceName, CountryRegionName, TerritoryName, \n\t      TerritoryGroup, SalesYTD, SalesLastYear\n\t      FROM Sales.vSalesPerson;\"]),\n\t  SalesRep = Table.SelectRows(Source, each([TerritoryName] <> null)),\n\t  RepName = Table.AddColumn(SalesRep, \"FullName\", each \n\t    if [MiddleName] <> null \n\t    then [FirstName]  & \" \" &  [MiddleName]  & \" \" & [LastName]\n\t    else [FirstName] & \" \" & [LastName]),\n\t  RemoveCols = Table.RemoveColumns(RepName,{\"FirstName\", \"MiddleName\", \"LastName\", \"JobTitle\"}),\n\t  RenameCols = Table.RenameColumns(RemoveCols,{{\"BusinessEntityID\", \"RepID\"}, \n\t    {\"StateProvinceName\", \"StateProvince\"}, \n\t    {\"CountryRegionName\", \"CountryRegion\"}, \n\t    {\"TerritoryName\", \"Territory\"}})\n\tin\n\t  RenameCols\n\t<\/pre>\n
    This time  around, we create a variable named RenameCols<\/b> and include in the definition the the \tTable.RenameColumns<\/b> function, which  takes two arguments. The first argument, as you’ve seen with other functions,  identifies our source table, RemoveCols<\/b>, and the second argument provides a list of columns to  rename. Each element in the list specifies the column’s original name and its  new name, with the names enclosed in their own set of curly braces. For example,  we’re changing the name of the BusinessEntityID<\/b> column to  \tRepID<\/b>,  and we’re change the name of the StateProvinceName<\/b> column to  \tStateProvince<\/b>.<\/p>\n
    Rounding numerical data<\/h1>\n
    We can also  add expression steps that round the numerical columns, \tSalesYTD<\/b> and SalesLastYear<\/b>,  but we have to take a slightly different approach from the previous two  examples. We’ll still call a Table<\/b>  function, Table.TransformColumns<\/b>,  which also takes two arguments, but the second argument includes a couple other  functions, as shown in the following let<\/b>  expression:<\/p>\n
    \tlet\n\t  Source = Sql.Database(\"localhost\\sqlsrv2014\", \"adventureworks2014\", \n\t    [Query=\"SELECT BusinessEntityID, FirstName, MiddleName, LastName, \n\t      JobTitle, City, StateProvinceName, CountryRegionName, TerritoryName, \n\t      TerritoryGroup, SalesYTD, SalesLastYear\n\t      FROM Sales.vSalesPerson;\"]),\n\t  SalesRep = Table.SelectRows(Source, each([TerritoryName] <> null)),\n\t  RepName = Table.AddColumn(SalesRep, \"FullName\", each \n\t    if [MiddleName] <> null \n\t    then [FirstName]  & \" \" &  [MiddleName]  & \" \" & [LastName]\n\t    else [FirstName] & \" \" & [LastName]),\n\t  RemoveCols = Table.RemoveColumns(RepName,{\"FirstName\", \"MiddleName\", \"LastName\", \"JobTitle\"}),\n\t  RenameCols = Table.RenameColumns(RemoveCols,{{\"BusinessEntityID\", \"RepID\"}, \n\t    {\"StateProvinceName\", \"StateProvince\"}, \n\t    {\"CountryRegionName\", \"CountryRegion\"}, \n\t    {\"TerritoryName\", \"Territory\"}}),\n\t  RoundSalesYTD = Table.TransformColumns(RenameCols,\n\t    {{\"SalesYTD\", each Number.Round(_, 0), type number}}),\n\t  RoundSalesLastYear = Table.TransformColumns(RoundSalesYTD,\n\t    {{\"SalesLastYear\", each Number.Round(_, 0), type number}})\n\tin\n\t  RoundSalesLastYear\n\t<\/pre>\n
    I’ve created  two expression steps to more easily explain the components, but we could have  instead used one step. The first step defines the \tRoundSalesYTD<\/b> variable. The \tTable.TransformColumns<\/b> function takes  two arguments: the source table, RenameCols<\/b>, and the transform operation. The transform operation is  made up of three components. The name of the target column, \tSalesYTD<\/b>, an expression that performs  the actual transformation, and the return type, \tnumber<\/b>. <\/p>\n
    The  transformation expression starts with the each<\/b> function, followed by the  \tNumber.Round<\/b> function, which does the actually rounding. This function also  takes two arguments: the value to round and the number of decimal points to  apply. In this case, the value to round is an underscore (_), which simply  represents the column’s current value, and the 0 indicates that the outputted  value should contain no decimal points.<\/p>\n
    The next  expression step, which defines the RoundSalesLastYear<\/b> variable works this same way, just with a different  column. If you want to instead turn these two steps into one, you can do  something like the following:<\/p>\n
    \tRoundCols = Table.TransformColumns(RenameCols, {\n\t  {\"SalesYTD\", each Number.Round(_, 0), type number}, \n\t  {\"SalesLastYear\", each Number.Round(_, 0), type number}})\n<\/pre>\n
    For now,  however, we’ll keep the expression steps separate and move on to our next  transformation.<\/p>\n
    Reordering columns<\/h1>\n
    You might  find that you don’t like the order of the columns in a data set, in which case  you can use the Table.ReorderColumns<\/b>  function, which also takes only two arguments: the source table, \tRoundSalesLastYear<\/b>, and the list of  columns in the order you want them:<\/p>\n
    \tlet\n\t  Source = Sql.Database(\"localhost\\sqlsrv2014\", \"adventureworks2014\", \n\t    [Query=\"SELECT BusinessEntityID, FirstName, MiddleName, LastName, \n\t      JobTitle, City, StateProvinceName, CountryRegionName, TerritoryName, \n\t      TerritoryGroup, SalesYTD, SalesLastYear\n\t      FROM Sales.vSalesPerson;\"]),\n\t  SalesRep = Table.SelectRows(Source, each([TerritoryName] <> null)),\n\t  RepName = Table.AddColumn(SalesRep, \"FullName\", each \n\t    if [MiddleName] <> null \n\t    then [FirstName]  & \" \" &  [MiddleName]  & \" \" & [LastName]\n\t    else [FirstName] & \" \" & [LastName]),\n\t  RemoveCols = Table.RemoveColumns(RepName,{\"FirstName\", \"MiddleName\", \"LastName\", \"JobTitle\"}),\n\t  RenameCols = Table.RenameColumns(RemoveCols,{{\"BusinessEntityID\", \"RepID\"}, \n\t    {\"StateProvinceName\", \"StateProvince\"}, \n\t    {\"CountryRegionName\", \"CountryRegion\"}, \n\t    {\"TerritoryName\", \"Territory\"}}),\n\t  RoundSalesYTD = Table.TransformColumns(RenameCols,\n\t    {{\"SalesYTD\", each Number.Round(_, 0), type number}}),\n\t  RoundSalesLastYear = Table.TransformColumns(RoundSalesYTD,\n\t    {{\"SalesLastYear\", each Number.Round(_, 0), type number}}),\n\t  ReorderCols = Table.ReorderColumns(RoundSalesLastYear,{\"RepID\", \"FullName\", \"City\", \n\t    \"StateProvince\", \"CountryRegion\", \"Territory\", \n\t    \"TerritoryGroup\", \"SalesLastYear\", \"SalesYTD\"})    \n\tin\n\t  ReorderCols\n\t<\/pre>\n
    All very  straightforward. The only trick is to enclose the list of columns in curly  braces, separate the column names with commas, and enclose each column name in  quotes.<\/p>\n
    Building on multiple  variables<\/h1>\n
    Now let’s  look at one final transformation in which we add another custom column. This  time, however, we’re going to use a series of embedded \tif<\/b> expressions to set the value. We’re also going to do something  else-introduce a second variable to add to the mix. Let’s start by looking at  the updated let<\/b> expression:<\/p>\n
    \tlet\n\t  Source = Sql.Database(\"localhost\\sqlsrv2014\", \"adventureworks2014\", \n\t    [Query=\"SELECT BusinessEntityID, FirstName, MiddleName, LastName, \n\t      JobTitle, City, StateProvinceName, CountryRegionName, TerritoryName, \n\t      TerritoryGroup, SalesYTD, SalesLastYear\n\t      FROM Sales.vSalesPerson;\"]),\n\t  SalesRep = Table.SelectRows(Source, each([TerritoryName] <> null)),\n\t  RepName = Table.AddColumn(SalesRep, \"FullName\", each \n\t    if [MiddleName] <> null \n\t    then [FirstName]  & \" \" &  [MiddleName]  & \" \" & [LastName]\n\t    else [FirstName] & \" \" & [LastName]),\n\t  RemoveCols = Table.RemoveColumns(RepName,{\"FirstName\", \"MiddleName\", \"LastName\", \"JobTitle\"}),\n\t  RenameCols = Table.RenameColumns(RemoveCols,{{\"BusinessEntityID\", \"RepID\"}, \n\t    {\"StateProvinceName\", \"StateProvince\"}, \n\t    {\"CountryRegionName\", \"CountryRegion\"}, \n\t    {\"TerritoryName\", \"Territory\"}}),\n\t  RoundSalesYTD = Table.TransformColumns(RenameCols,\n\t    {{\"SalesYTD\", each Number.Round(_, 0), type number}}),\n\t  RoundSalesLastYear = Table.TransformColumns(RoundSalesYTD,\n\t    {{\"SalesLastYear\", each Number.Round(_, 0), type number}}),\n\t  ReorderCols = Table.ReorderColumns(RoundSalesLastYear,{\"RepID\", \"FullName\", \"City\", \n\t    \"StateProvince\", \"CountryRegion\", \"Territory\", \n\t    \"TerritoryGroup\", \"SalesLastYear\", \"SalesYTD\"}),\n\t \n\t  \/\/calculate last year's average\n\t  LastYearAvg = List.Average(Table.Column(ReorderCols, \"SalesLastYear\")),\n\t  SalesStatus = Table.AddColumn(ReorderCols, \"SalesStatus\", each \n\t    if [SalesLastYear] = 0 and [SalesYTD] > LastYearAvg\/2\n\t      then \"Great start\"\n\t    else if [SalesYTD] > LastYearAvg * 3\n\t      then \"Top performer\"\n\t    else if [SalesYTD] > LastYearAvg * 2\n\t      then \"Excellent showing\"\n\t    else if [SalesYTD] > LastYearAvg\n\t      then \"Better than average\"\n\t    else \"Slacker\")\n\tin\n\t    SalesStatus\n\t<\/pre>\n
    The new  section of code starts with a comment, as indicated by the double forward  slashes. I threw this in just to show you that PQFL supports one-line comments  like this and multi-line comments that follow T-SQL conventions (\/* and *\/).  <\/p>\n
    Next, we  define a variable whose sole purpose is to capture the average amount of sales  from the SalesLastYear<\/b> column. To get  the average, we add the LastYearAvg<\/b>  variable and start the definition with the \tList.Average<\/b> function. We’re using a \tList<\/b> function because the \tTable<\/b> object doesn’t support an \tAverage<\/b> function. The \tList.Average<\/b> function takes one  argument, which itself calls the Table.Column<\/b> function. The  \tTable.Column<\/b> function merely retrieves the \tSalesLastYear<\/b> values from the \tReorderCols<\/b> table so that the \tList.Average<\/b> function can calculate  the average of those values. On my system, the average comes to 1691854.5446.<\/p>\n
    We can now  use the LastYearAvg<\/b> variable in  subsequent expression steps as part of our calculations. The next step, then,  defines the SalesStatus<\/b> variable,  which uses the Table.AddColumn<\/b>  function to insert a custom column into the data set. Notice that the function’s  first argument refers to the ReorderCols<\/b>  variable, not the LastYearAvg<\/b>  variable. We want to return to our main data set and work from there.<\/p>\n
    The \tSalesStatus<\/b> definition is similar to  the RepName<\/b> definition, except that  the embedded if<\/b> expressions are  somewhat more involved with SalesStatus<\/b>.  What we’re trying to do here is assign values to the new column based on how  each SalesYTD<\/b> value compares to the  average sales for the previous year, as determined by the \tLastYearAvg<\/b> variable. (PQFL doesn’t support anything comparable to a \tCASE<\/b> expression.) Essentially, we’re  just adding an if<\/b> expression to the \telse<\/b> clause of the previous \tif<\/b> expression.<\/p>\n
    The logic in  these if <\/b>expressions is fairly  straightforward. A value is assigned to the column based on how it evaluates  against last year’s average. Your final data set should now look similar to  what’s shown in the following figure.<\/p>\n
     \t \t\"2285-img3F.jpg\"<\/p>\n
    The main  point to take out of this last transformation is that you don’t have to stick  strictly to the formula of defining one variable after the other and using that  variable in the next step. The steps must still proceed lineally, so you have to  account for that, but you also have room to play.<\/p>\n
    Creating visualizations<\/h1>\n
    Once you  have applied and saved your data set, you can use it just like any other data  set. For example, I created the following visualization based on the \tFullName<\/b>, \tSalesYTD<\/b>, and SalesStatus<\/b>  columns.<\/p>\n
     \t \t\"2285-clip_image010.gif\"<\/p>\n
    A simple,  yet telling visualization, which is exactly what a visualization is supposed to  achieve. And this is only the beginning of what you can do with PQFL in Power BI  Desktop. You can retrieve data from a wide range of data sources, work with any  of the object types in a single query, and apply an assortment of  transformations. You can even use the UI component to do some of your  transformations and fill in the rest directly in your PQFL query.  <\/p>\n
    The biggest  stumbling blocks you’ll likely run into are the lack of concise documentation in  many areas and the inadequate script editor. However, once you have a solid  foundation of how the PQFL pieces fit together, you’ll be amazed at what you can  do with what you’ve got.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
    The Power Query Formula Language (PQFL) is a functional language that drives the Power BI transformations, and allows you to create mashup queries from scratch. Rob demonstrates how to use it in Power BI Desktop to extract data from its source, filter rows, specify the columns, clean the data, and create visualisations.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[4178,4379,4179,4150],"coauthors":[],"class_list":["post-2090","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-bi","tag-reporting-services","tag-source-control","tag-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2090","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2090"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2090\/revisions"}],"predecessor-version":[{"id":91005,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2090\/revisions\/91005"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2090"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2090"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2090"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2090"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}