{"id":80057,"date":"2018-08-01T13:59:43","date_gmt":"2018-08-01T13:59:43","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=80057"},"modified":"2026-04-15T19:17:14","modified_gmt":"2026-04-15T19:17:14","slug":"power-bi-introduction-power-query-m-formula-language-in-power-bi-desktop-part-6","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/power-bi-introduction-power-query-m-formula-language-in-power-bi-desktop-part-6\/","title":{"rendered":"Power Query M Language in Power BI: Data Transformation Step-by-Step Guide"},"content":{"rendered":"\n

Executive Summary<\/h2>\n\n\n\n

Power Query M is the formula language that Power BI Desktop uses behind the scenes when you apply data transformations in the Power Query Editor. Each transformation step you perform in the UI generates an M expression in a let statement – a chain of named steps where each step builds on the previous one. This article demonstrates writing M expressions directly, building a complete data transformation pipeline on a CSV dataset (the Titanic passenger data): loading the file, removing an unwanted column, promoting the first row to headers, renaming columns, filtering out null values, replacing coded values with readable text, changing capitalisation, and adding a calculated column. Writing M directly gives you more control and portability than the UI alone.<\/strong><\/p>\n\n\n

The series so far:<\/strong><\/p>\n

    \n
  1. Power BI Introduction: Tour of Power BI \u2014 Part 1<\/a><\/li>\n
  2. Power BI Introduction: Working with Power BI Desktop \u2014 Part 2<\/a><\/li>\n
  3. Power BI Introduction: Working with R Scripts in Power BI Desktop \u2014 Part 3<\/a><\/li>\n
  4. Power BI Introduction: Working with Parameters in Power BI Desktop \u2014 Part 4<\/a><\/li>\n
  5. Power BI Introduction:\u00a0Working with SQL Server data in Power BI Desktop \u2014 Part 5<\/a><\/li>\n
  6. Power BI Introduction:\u00a0Power Query M Formula Language in Power BI Desktop \u2014 Part 6<\/a><\/li>\n
  7. Power BI Introduction: Building Reports in Power BI Desktop \u2014 Part 7<\/a><\/li>\n
  8. Power BI Introduction: Publishing Reports to the Power BI Service \u2014 Part 8<\/a><\/li>\n
  9. Power BI Introduction: Visualizing SQL Server Audit Data \u2014 Part 9<\/a><\/li>\n<\/ol>\n\n\n\n\n

    In Power BI Desktop, a dataset is defined by a single query that specifies what data to include and how to transform that data. The query is made up of related steps that build on each other to produce the final dataset. Once you\u2019ve defined the dataset, you can use it to create visualizations that you add to your reports, which you can then publish to the Power BI service.<\/p>\n\n\n\n

    At the heart of the query is the Power Query M formula language, a case-sensitive, data mashup language available to Power BI Desktop, Power Query in Excel, and the Get & Transform<\/em> import feature in Excel 2016. As with many languages, Power Query statements are made up of individual language elements such as functions, variables, expressions, and primitive and structured values that together define the logic necessary to shape the data.<\/p>\n\n\n\n

    Each query in Power BI Desktop is a single Power Query let<\/strong> expression that contains all the code elements necessary to define a dataset. A let <\/strong>expression is made up of a let<\/strong> statement and an in<\/strong> statement, as shown in the following syntax:<\/p>\n\n\n\n

    let  \n   variable<\/em> = expression<\/em> [,...]\nin   \n   variable<\/em><\/pre>\n\n\n\n
    The let<\/strong> statement includes one or more procedural steps that define the query. Each step is essentially a variable assignment that consists of a variable name and an expression that provides the variable\u2019s value. The expression defines the logic for adding, removing, or transforming the data in a specific way. This is where you\u2019ll be doing the bulk of your work, as you\u2019ll see in the examples to follow.<\/p>\n\n\n\n
    The variable can be of any supported Power Query type. Each variable within a let<\/strong> expression must have a unique name, but Power Query is fairly flexible about what you call it. You can even include spaces in the name, but you have to enclose it in double-quotes and precede it with a hash tag, a somewhat cumbersome naming convention (and one I prefer to avoid). The variable names are also the same names used to identify steps in the Applied Steps<\/em> section in Query Editor\u2019s right pane, so use names that make sense.<\/p>\n\n\n\n
    You can include as many procedural steps in your let<\/strong> statement as necessary and practical. If you include multiple steps, you must use commas to separate them. Each step generally builds on the preceding one, using the variable from that step to define the logic in the new step.<\/p>\n\n\n\n
    Strictly speaking, you do not have to define your procedural steps in the same physical order as their logical order. For example, you can reference a variable in the first procedural step that you define in the last procedural step. However, this approach can make the code difficult to debug and cause unnecessary confusion. The accepted convention when writing a let <\/strong>statement is to keep the physical and logical orders in sync.<\/p>\n\n\n\n
    The in<\/strong> statement returns a variable value that defines the dataset\u2019s final shape. In most cases, if not all, this will be the last variable defined in your let<\/strong> statement. You can specify a different variable from the let<\/strong> statement, but there\u2019s usually little reason to do. If you simply need to see a variable\u2019s value at a specific point in time, you can select its related step in the Applied Steps<\/em> section in Query Editor. When you select a step, you are essentially viewing the contents of the associated variable.<\/p>\n\n\n\n
    Retrieving Data from a CSV File<\/h2>\n\n\n\n
    This article includes a number of examples that demonstrate how to define the procedural steps necessary to build a query. The examples are based on the titanic<\/em> dataset available from this GitHub page<\/a>. The titanic<\/em> dataset lists the passengers on the infamous Titanic voyage and shows who survived and who did not. If you plan to try these examples for yourself, you need to create the titanic.csv<\/em> file from the titanic<\/em> dataset and save it to a folder that you can access from Power BI Desktop. On my system, I saved the file to my local folder C:\\DataFiles\\titanic.csv<\/em>. There are several files named \u2018titanic\u2019 on the GitHub page, so be sure to download the Stat2Data Titanic file.<\/p>\n\n\n\n
    The first step in building your own Power Query script is to add a blank query to Power BI Desktop. To add the query, click Get Data<\/em> on the Home<\/em> ribbon in the main window, navigate to the Other<\/em> section, and double-click Blank Query.<\/em> This launches Query Editor with a new query listed in the Queries<\/em> pane. The query is named Query1<\/em> or a similarly numbered name, depending on what queries you\u2019ve already created.<\/p>\n\n\n\n
    To rename the query, right-click the query in the Queries<\/em> pane, click Rename,<\/em> type SurvivalLog,<\/em> and press Enter<\/em>. Then, on the View<\/em> menu, click Advanced Editor<\/em>. The editor will open with a new let<\/strong> expression defined, as shown in the following figure.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The let<\/strong> statement includes a single procedural step in which the variable is named Source<\/strong> and the expression is an empty string, as indicated by the double quotes. Notice that the variable is also listed in the in<\/strong> statement and as a step in the Applied Steps<\/em> section of the right pane. For this exercise, you\u2019ll be changing the variable name to GetPassengers<\/strong>.<\/p>\n\n\n\n
    The first procedural step that you\u2019ll add will retrieve the contents of the titanic.csv<\/em> file and save them to the GetPassengers<\/strong> variable. To add the procedure, replace the existing let<\/strong> expression with the following expression:<\/p>\n\n\n\n
    let  GetPassengers = Csv.Document(File.Contents(\"C:\\DataFiles\\titanic.csv\"),     [Delimiter=\",\", Encoding=1252])in  GetPassengers<\/pre>\n\n\n\n
    Notice that the GetPassengers<\/strong> variable is specified in the procedural step in the let<\/strong> statement and in the in<\/strong> statement. In this way, the variable\u2019s value will be returned when the let<\/strong> expression runs.<\/p>\n\n\n\n
    The expression in the procedural step includes everything after the equal sign. The expression uses the Csv.Document<\/strong> function to retrieve the file\u2019s contents as a Table<\/strong> object. The function supports several parameters, but only the first one is required to identify the source data. As part of the first parameter, you must also use the File.Contents<\/strong> function to return only the document\u2019s data.<\/p>\n\n\n\n
    The second parameter can be specified as a record<\/em> that contains optional settings. In Power Query, a record is a set of fields made up of name\/value pairs enclosed in brackets. The record in this example includes the Delimiter<\/strong> and Encoding<\/strong> options and their values. The Delimiter<\/strong> option specifies a comma as the delimiter in the CSV document, and the Encoding<\/strong> option specifies the text encoding type of 1252<\/strong>, which is based on the Windows Western European code page.<\/p>\n\n\n\n
    That\u2019s all there is to setting up a basic let<\/strong> expression. After you\u2019ve entered your code, click Done<\/em> to close the Advanced Editor and run the procedural step. The dataset, as it appears in Query Editor, should look similar to the following figure.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Notice that the first step in the Applied Steps<\/em> section is named GetPassengers,<\/em> the same as the variable name. The procedural step\u2019s expression is also displayed in the small window above the dataset. You can edit the code directly in this window if necessary.<\/p>\n\n\n\n
    Whenever you add, modify, or delete a step, you should apply and save your changes. A simple way to do this is to click the Save<\/em> icon at the top left corner of the menu bar. When prompted to apply your changes, click Apply<\/em>.<\/p>\n\n\n\n
    Removing a Column from the Dataset<\/h2>\n\n\n\n
    The next procedural step you will add to your let<\/strong> statement removes Column7<\/em> from the dataset. Because your dataset is saved as a Table<\/strong> object, you can choose from a variety of Power Query Table<\/strong> functions to update the data. For this step, you\u2019ll use the Table.RemoveColumns<\/strong> function to filter out the specified column.<\/p>\n\n\n\n
    Click Advanced Editor<\/em> to get back to the query. To remove the column, add a comma after the first procedural step and then, on a new line, define the next variable\/expression pair, as shown in the following code:<\/p>\n\n\n\n
    let  GetPassengers = Csv.Document(File.Contents(\"C:\\DataFiles\\titanic.csv\"),     [Delimiter=\",\", Encoding=1252]),  RemoveCols = Table.RemoveColumns(GetPassengers, \"Column7\")in  RemoveCols<\/pre>\n\n\n\n
    The Table.RemoveColumns<\/strong> function requires two parameters. The first parameter specifies the target table that you\u2019ll be updating, which in this case, is the GetPassengers<\/strong> variable from the previous step. The second argument specifies a single column or a list of columns to be removed. This example specifies only Column7<\/strong> from the GetPassengers<\/strong> table. The important takeaway here is how the second step builds on the first one and then returns a new result, which is assigned to the RemoveCols<\/strong> variable. The new variable contains the same dataset as the GetPassengers<\/strong> variable, less the Column7<\/strong> data.<\/p>\n\n\n\n
    After you add the procedural step, replace the GetPassengers<\/strong> variable in the in <\/strong>statement with the RemoveCols<\/strong> variable. Next, click Done<\/strong> to close the Advanced Editor, and then save and apply your changes. You dataset should now look like the one shown in the following figure.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    As you can see, the RemoveCols<\/em> step has been added to the Applied Steps<\/em> section and Column7<\/em> is no longer included in the dataset. To add more procedural steps, follow the same logic as here, continuing to build on your growing foundation.<\/p>\n\n\n\n
    Promoting the First Row to Headers<\/h2>\n\n\n\n
    The next step is to promote the values in the first row of your dataset to the header row so these values become the column names. To carry out this step, add a comma after the last step you defined and then, on a new line, type the following variable\/expression pair:<\/p>\n\n\n\n
    PromoteNames = Table.PromoteHeaders(RemoveCols, [PromoteAllScalars=true])<\/pre>\n\n\n\n
    The expression uses the Table.PromoteHeaders<\/strong> function to promote the first row to the column headers. The function\u2019s first parameter is required and specifies the table to use as the source data (the RemoveCols<\/strong> variable). This is just like you saw in the previous example, in which the new step builds on the previous step.<\/p>\n\n\n\n
    The second parameter, PromoteAllScalars<\/strong>, is optional, but is a good one to know about. By default, Power Query promotes only text and number values. When the PromoteAllScalars<\/strong> parameter is included and set to true<\/strong>, Power Query promotes all scalar values in the first row to headers.<\/p>\n\n\n\n
    The expression\u2019s results are assigned to the PromoteNames<\/strong> variable. Be sure to update the in<\/strong> statement with this variable name.<\/p>\n\n\n\n
    Renaming Dataset Columns<\/h2>\n\n\n\n
    Next, you will use the Table.RenameColumns<\/strong> function to rename several columns. The function returns a new Table <\/strong>object with the column names updated as specified. To rename the columns, add the following procedural step to your let<\/strong> statement, being sure to insert a comma after the previous statement:<\/p>\n\n\n\n
    RenameCols = Table.RenameColumns(PromoteNames, {{\"\", \"PsgrID\"}, \n  {\"PClass\", \"PsgrClass\"}, {\"Sex\", \"Gender\"}})<\/pre>\n\n\n\n
    The Table.RenameColumns<\/strong> function requires two parameters. The first is the name of the target table (the PromoteNames<\/strong> variable from the previous step), and the second is a list<\/em> of old and new column names. In Power Query, a list is an ordered sequence of values, separated by commas and enclosed in curly brackets. In this example, each value in the list is its own list containing two values: the old column name and the new column name.<\/p>\n\n\n\n
    Filtering Rows in the Dataset<\/h2>\n\n\n\n
    The next task is to filter out rows that contain NA<\/strong> in the Age<\/strong> columns and then filter out rows with Age<\/strong> values 5<\/strong> or less. Because the Age<\/strong> column was automatically typed as Text<\/strong> when imported, you will need to filter the data in three steps, starting with the NA<\/strong> values. To filter out these values, add the following variable\/expression pair to your code:<\/p>\n\n\n\n
    FilterNA = Table.SelectRows(RenameCols, each [Age] <> \"NA\")<\/pre>\n\n\n\n
    The Table.SelectRows<\/strong> function returns a table that contains only the rows that match the defined condition (Age<\/strong> not equal to NA<\/strong>). As you saw in the previous examples, the function\u2019s first argument is target table, in this case, the RenameCols<\/strong> variable from the preceding step.<\/p>\n\n\n\n
    The second argument is an each<\/strong> expression specifying that the Age<\/strong> value must not equal NA<\/strong>. The each<\/strong> keyword indicates that the expression should be applied to each row in the target table. As a result, the new table, which is assigned to the FilterNA<\/strong> variable, will not include any rows with an Age<\/strong> value of NA<\/strong>.<\/p>\n\n\n\n
    Once you\u2019ve remove the NA<\/strong> values, you should convert the Age<\/strong> column to the Number<\/strong> data type so you can work with the data more effectively, such as being able to filter the data based on a numerical age. When changing the type of the Age<\/strong> column, you can also change the type of the PsgrID<\/strong> column to the Int64<\/strong> type. This way, you\u2019re referencing the IDs by integers rather than text. To convert to the new data types, add the following variable\/expression pair to the let <\/strong>statement:<\/p>\n\n\n\n
    ChangeTypes = Table.TransformColumnTypes(FilterNA, \n  {{\"PsgrID\", Int64.Type}, {\"Age\", Number.Type}})<\/pre>\n\n\n\n
    The expression uses the Table.TransformColumnTypes<\/strong> function to change the data types. The first parameter is the name of the target table (FilterNA<\/strong>), and the second parameter is a list of the columns to be updated, along with their new data types. Each value is its own list that includes the name of the column and type.<\/p>\n\n\n\n
    Once the Age<\/strong> column has been updated with the Number<\/strong> type, you can filter out the ages based on a numerical value, as shown in the following procedural step.<\/p>\n\n\n\n
    FilterKids = Table.SelectRows(ChangeTypes, each [Age] > 5)<\/pre>\n\n\n\n
    Your data set should now include only the desired data, as it exists in the FilterKids<\/strong> variable.<\/p>\n\n\n\n
    Replacing Column Values in a Dataset<\/h2>\n\n\n\n
    Another handy transformation you might need to do at times is to replace one column value for another. For this example, you will replace the 0<\/strong> value in the Survived<\/strong> column with No<\/strong> and the 1<\/strong> value with Yes<\/strong>, using the Table.ReplaceValue<\/strong> function. To replace the 0<\/strong> values, add the following procedural step to your code:<\/p>\n\n\n\n
    Replace0 = Table.ReplaceValue(FilterKids, \"0\", \"No\", \n  Replacer.ReplaceText, {\"Survived\"})<\/pre>\n\n\n\n
    The Table.ReplaceValue<\/strong> function requires the following five parameters:<\/p>\n\n\n
    \n
      \n
    • table:<\/strong> The target table, in this case, the variable from the preceding step.<\/li>\n\n\n\n
    • oldValue:<\/strong> The original value to be replaced.<\/li>\n\n\n\n
    • newValue:<\/strong> The value that will replace the original value.<\/li>\n\n\n\n
    • replacer:<\/strong> A Replacer<\/strong> function that carries out the replacement operation.<\/li>\n\n\n\n
    • columnsToSearch:<\/strong> The column or columns whose values should be replaced.<\/li>\n<\/ul>\n<\/div>\n\n\n
      Most of the parameters should be self-explanatory, except perhaps the replacer<\/strong> parameter. For this, you can choose from several functions that work in conjunction with the Table.ReplaceValue<\/strong> function to update the values. In this case, the Replacer.ReplaceText<\/strong> function is used because text values are being replaced.<\/p>\n\n\n\n
      After you replace the 0<\/strong> values, you can replace the 1<\/strong> values with Yes<\/strong>, using the same construction:<\/p>\n\n\n\n
      Replace1 = Table.ReplaceValue(Replace0, \"1\", \"Yes\",   Replacer.ReplaceText, {\"Survived\"})<\/pre>\n\n\n\n
      Now the values in the Survived<\/strong> column are more readable for those who might not understand the meaning of the 0<\/strong> and 1<\/strong> values. It also makes it less likely that the data will cause any confusion.<\/p>\n\n\n\n
      Changing the Case of Column Values<\/h2>\n\n\n\n
      Another way you can modify column values is to change how they are capitalized. For this example, you will update the values in the Gender<\/strong> column so the first letter is capitalized, rather than being all lowercase. To make these changes, add the following procedural step to the let<\/strong> statement:<\/p>\n\n\n\n
      ChangeCase = Table.TransformColumns(Replace1, {\"Gender\", Text.Proper})<\/pre>\n\n\n\n
      The expression uses the Table.TransformColumns<\/strong> function to update the values. The function requires two parameters. The first is the table to be updated, and the second is a list of operations to carry out. For each operation, you must provide the target column and an expression that carries out the operation. (At times, you might need to provide additional information.) For this example, there is only one operation, so you need only specify the Gender<\/strong> column and the Text.Proper<\/strong> function, enclosed in curly braces. The function converts the first letter in each word in the Gender<\/strong> column to uppercase.<\/p>\n\n\n\n
      At this point, your let<\/strong> expression, in its entirety, should look similar to the following code:<\/p>\n\n\n\n
      let\n  GetPassengers = Csv.Document(File.Contents(\"C:\\DataFiles\\titanic.csv\"), \n    [Delimiter=\",\", Encoding=1252]),\n  RemoveCols = Table.RemoveColumns(GetPassengers, \"Column7\"),\n  PromoteNames = Table.PromoteHeaders(RemoveCols, [PromoteAllScalars=true]),\n  RenameCols = Table.RenameColumns(PromoteNames, {{\"\", \"PsgrID\"}, \n    {\"PClass\", \"PsgrClass\"}, {\"Sex\", \"Gender\"}}),\n  FilterNA = Table.SelectRows(RenameCols, each [Age] <> \"NA\"),\n  ChangeTypes = Table.TransformColumnTypes(FilterNA, \n    {{\"PsgrID\", Int64.Type}, {\"Age\", Number.Type}}),\n  FilterKids = Table.SelectRows(ChangeTypes, each [Age] > 5),\n  Replace0 = Table.ReplaceValue(FilterKids, \"0\", \"No\", \n    Replacer.ReplaceText, {\"Survived\"}),  Replace1 = Table.ReplaceValue(Replace0, \"1\", \"Yes\",     Replacer.ReplaceText, {\"Survived\"}),  ChangeCase = Table.TransformColumns(Replace1, {\"Gender\", Text.Proper})\nin\n  ChangeCase<\/pre>\n\n\n\n
      The let<\/strong> statement includes all the steps that makes up your query to this point, with each step building on the preceding step. The following figure shows how the dataset should now appear in Query Editor.<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      <\/p>\n\n\n\n
      Notice that the Applied Steps<\/em> section includes one step for each variable defined in the let<\/strong> statement, specified in the same order they appear in the statement. If you select a step, the data displayed in the main windows will reflect the contents of the related variable.<\/p>\n\n\n\n
      Adding a Calculated Column to a Dataset<\/h2>\n\n\n\n
      Up to this point, each procedural step in your let<\/strong> statement represents a discrete action that is built on the preceding step. In some cases, however, you might need to structure your steps in a way that is not quite so linear. For example, you might want to create a calculated column that shows the difference between a passenger\u2019s age and the average age for that person\u2019s gender. The following steps outline one approach you can take to this column:<\/p>\n\n\n
      \n
        \n
      1. Calculate the average age of the female passengers and save it to a variable.<\/li>\n\n\n\n
      2. Calculate the average age of the male passengers and save it to a variable.<\/li>\n\n\n\n
      3. Add a column that uses the two variables to calculate the age differences.<\/li>\n\n\n\n
      4. Round the age differences to a more readable number of decimals. (This step is optional.)<\/li>\n<\/ol>\n<\/div>\n\n\n
        To carry out these steps, add the following procedural steps to your let<\/strong> statement:<\/p>\n\n\n\n
        \/\/ add calculated column based on average ages\nFemale = Table.SelectRows(ChangeCase, each [Gender] = \"Female\"),\nAvgFemale = List.Average(Table.Column(Female, \"Age\")),\nMale = Table.SelectRows(ChangeCase, each [Gender] = \"Male\"),\nAvgMale = List.Average(Table.Column(Male, \"Age\")),\nAddCol = Table.AddColumn(ChangeCase, \"AgeDiff\", each   if [Gender] = \"Female\" then [Age] - AvgFemale  else [Age] - AvgMale),RoundDiff = Table.TransformColumns(AddCol,   {\"AgeDiff\", each Number.Round(_, 2)})<\/pre>\n\n\n\n
        I\u2019ll walk you through these steps in smaller chunks so you can better understand how they work. The first line, which is preceded by two forward slashes, indicates a single-line comment. Anything after the slashes to the end of the line is informational only and does not get processed. Power Query also supports multi-line comments that begin with \/* and end with *\/, just like multi-line comments in SQL Server.<\/p>\n\n\n\n
        It\u2019s up to you whether you want to include comments. Whether or not you do, the next step is to calculate the average age of the female passengers. The following two procedural steps give you the value of the age of the average female.<\/p>\n\n\n\n
        Female = Table.SelectRows(ChangeCase, each [Gender] = \"Female\"),\nAvgFemale = List.Average(Table.Column(Female, \"Age\")),<\/pre>\n\n\n\n
        The first step uses the Table.SelectRows<\/strong> function to generate a table that includes only rows with a Gender<\/strong> value of Female<\/strong>. The function is being used here in the same way you saw earlier, except that different data is being filtered out and the results are being saved to the Female<\/strong> variable. Notice that the source table for the function is the ChangeCase<\/strong> variable from the preceding step.<\/p>\n\n\n\n
        The second step uses the List.Average<\/strong> function to calculate that average for the Age<\/strong> values in the Female<\/strong> table. The expression returns a scalar value, which is saved to the AvgFemale<\/strong> variable. The function requires only one parameter. The parameter includes a second function, Table.Column<\/strong>, which passes the values from the Age<\/strong> column to the List.Average<\/strong> function.<\/p>\n\n\n\n
        The next step is to find the average age of the male passengers. For this, you can use the same construction, with a few minor changes:<\/p>\n\n\n\n
        Male = Table.SelectRows(ChangeCase, each [Gender] = \"Male\"),\nAvgMale = List.Average(Table.Column(Male, \"Age\")),<\/pre>\n\n\n\n
        Notice that you must again use the ChangeCase<\/strong> variable for the source table when calling the Table.SelectRows<\/strong> function, even though it is no longer the step directly preceding the new one. You can, in fact, use any of the previous variables in your expressions, as long as it makes sense to do so.<\/p>\n\n\n\n
        With the AvgFemale<\/strong> and AvgMale<\/strong> variables in place, you can now add the column, using the Table.AddColumn<\/strong> function:<\/p>\n\n\n\n
        AddCol = Table.AddColumn(ChangeCase, \"AgeDiff\", each   if [Gender] = \"Female\" then [Age] - AvgFemale  else [Age] - AvgMale),<\/pre>\n\n\n\n
        The Table.AddColumn<\/strong> function requires three parameters. The first is the target table, which is again ChangeCase<\/strong>, and the second is the name of the new column, AgeDiff<\/strong>.<\/p>\n\n\n\n
        The third argument is the expression used to generate the column\u2019s values. The expression starts with the each<\/strong> keyword, which iterates through each row in the target table. This is followed by an if\u2026then\u2026else<\/strong> expression that calculates the row\u2019s value based on whether the passenger is male or female. If the Gender<\/strong> value equals Female<\/strong>, then the AgeDiff<\/strong> value is set to the Age<\/strong> value minus the AvgFemale<\/strong> value, otherwise set the AgeDiff<\/strong> value is set to the Age<\/strong> value minus the AvgMale<\/strong> value.<\/p>\n\n\n\n
        After defining the new column, the final procedural step rounds the AgeDiff<\/strong> values to two decimal points:<\/p>\n\n\n\n
        RoundDiff = Table.TransformColumns(AddCol,   {\"AgeDiff\", each Number.Round(_, 2)})<\/pre>\n\n\n\n
        The expression includes the Table.TransformColumns<\/strong> function, which you saw earlier, but uses the Number.Round<\/strong> function to round the values, rather than changing their case. The Number.Round<\/strong> function takes two parameters. The first value is an underscore, which represents the column\u2019s current value. The second value, 2<\/strong>, indicates that the value should be rounded to two decimal places.<\/p>\n\n\n\n
        That completes the steps necessary to create the calculated column. Your let<\/strong> expression, in its entirety, should now look like the following code:<\/p>\n\n\n\n
        let\n  GetPassengers = Csv.Document(File.Contents(\"C:\\DataFiles\\titanic.csv\"), \n    [Delimiter=\",\", Encoding=1252]),\n  RemoveCols = Table.RemoveColumns(GetPassengers, \"Column7\"),\n  PromoteNames = Table.PromoteHeaders(RemoveCols, [PromoteAllScalars=true]),\n  RenameCols = Table.RenameColumns(PromoteNames, {{\"\", \"PsgrID\"}, \n    {\"PClass\", \"PsgrClass\"}, {\"Sex\", \"Gender\"}}),\n  FilterNA = Table.SelectRows(RenameCols, each [Age] <> \"NA\"),\n  ChangeTypes = Table.TransformColumnTypes(FilterNA, \n    {{\"PsgrID\", Int64.Type}, {\"Age\", Number.Type}}),\n  FilterKids = Table.SelectRows(ChangeTypes, each [Age] > 5),\n  Replace0 = Table.ReplaceValue(FilterKids, \"0\", \"No\", \n    Replacer.ReplaceText, {\"Survived\"}),\n  Replace1 = Table.ReplaceValue(Replace0, \"1\", \"Yes\", \n    Replacer.ReplaceText, {\"Survived\"}),\n  ChangeCase = Table.TransformColumns(Replace1, {\"Gender\", Text.Proper}),\n  \/\/ add calculated column based on average ages\n  Female = Table.SelectRows(ChangeCase, each [Gender] = \"Female\"),\n  AvgFemale = List.Average(Table.Column(Female, \"Age\")),\n  Male = Table.SelectRows(ChangeCase, each [Gender] = \"Male\"),\n  AvgMale = List.Average(Table.Column(Male, \"Age\")),\n  AddCol = Table.AddColumn(ChangeCase, \"AgeDiff\", each     if [Gender] = \"Female\" then [Age] - AvgFemale    else [Age] - AvgMale),  RoundDiff = Table.TransformColumns(AddCol,     {\"AgeDiff\", each Number.Round(_, 2)})in  RoundDiff<\/pre>\n\n\n\n
        The following figure shows what your dataset should now look like in Query Editor.<\/p>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        <\/p>\n\n\n\n
        Notice that the Applied Steps<\/em> section includes the variables used to find the average ages, even though the AddCol<\/strong> step in the code builds on the ChangeCase<\/strong> variable. You can view the contents of these variables by selecting the step in the Applied Steps<\/em> section. For example, the following figure shows the data saved to the Female<\/strong> variable.<\/p>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        <\/p>\n\n\n\n
        If you select one of the variables used to store the actual average, Query Editor will display only its scalar value, as shown in the following figure.<\/p>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        <\/p>\n\n\n\n
        Now Query Editor shows only the contents of the AvgFemale<\/strong> variable, a rather lengthy number.<\/em> You can see why I included the final procedural step to round the values in the calculated column.<\/p>\n\n\n\n
        Navigating the Power Query M Formula Language<\/h2>\n\n\n\n
        Not surprisingly, there\u2019s much more to Power Query that what was covered here, but this should give you the foundation you need to start building your own queries in Power BI Desktop. It should also give you a better understanding of how queries are constructed when using point-and-click features to import and transform your data. In this way, you can examine the code to better to understand why you might not be getting the results you expect. You can also start building your query by using point-and-click operations and then use the Advanced Editor to fine-tune your datasets or introduce logic not easily achieved through the interface.<\/p>\n\n\n\n
        To make the most of Power Query in Power BI Desktop, you\u2019ll need to dig a lot deeper into the language\u2019s various elements than what we\u2019ve covered here, especially the built-in functions. Keep in mind, though, that Power Query was built with Excel in mind, so you might run into elements that don\u2019t transfer easily to the Power BI Desktop environment. Even so, the basic principles that define the language and its syntax are the same, and the better you understand them, the more powerful a tool you have for working with data in Power BI Desktop.<\/p>\n\n\n\n
        \n    
        FAQs: Power BI Introduction: Power Query M Formula Language in Power BI Desktop \u2014 Part 6<\/h2>\n\n                        
        1. What is the Power Query M language in Power BI?<\/h3>\n            
        \n                
        Power Query M (also called the M formula language or M language) is the functional programming language that Power BI Desktop uses to define data transformation queries. Every transformation you apply in the Power Query Editor UI generates M code in a let…in expression. The let block defines a series of named steps (Step1 = expression, Step2 = transform(Step1), …) and the in clause specifies the final result. You can view and edit the M code directly in the Advanced Editor. M is case-sensitive and functional – each step is a pure function applied to the previous result.<\/p>\n            <\/div>\n                    
        2. How do I view and edit Power Query M code in Power BI?<\/h3>\n            
        \n                
        In Power BI Desktop, open Power Query Editor (Home > Transform Data). Select a query in the left panel. Click View > Advanced Editor to see the complete M code for that query. You can edit the M code directly in the Advanced Editor and click Done to apply changes. Individual step formulas are also visible in the formula bar when you click on a step in the Applied Steps panel. The Power Query M function reference documentation at docs.microsoft.com\/en-us\/powerquery-m lists all available M functions with examples.<\/p>\n            <\/div>\n                    
        3. What is the difference between Power Query M and DAX in Power BI?<\/h3>\n            
        \n                
        Power Query M and DAX serve different purposes. M runs during data loading and transformation (the ETL stage) – it cleans, reshapes, combines, and filters data before it is loaded into the Power BI data model. DAX runs at report query time – it performs calculations and aggregations on the already-loaded data model, responding to filters and slicers. M changes what data enters the model; DAX changes how the model is analysed. In practice: use M for data cleaning (remove columns, fix types, merge tables); use DAX for analytics (measures, calculated columns, time intelligence).<\/p>\n            <\/div>\n                    
        4. How do I combine data from multiple sources in Power Query M?<\/h3>\n            
        \n                
        Use Table.Combine([Table1, Table2]) to stack tables vertically (same columns). Use Table.NestedJoin or Table.Join to merge tables horizontally (lookup\/join on a key column). For CSV files in a folder, use Folder.Contents() to retrieve all files as a binary table, then combine using Table.Combine(List.Transform(…)). In the UI, these operations are the Append Queries (vertical combine) and Merge Queries (horizontal join) commands under the Home tab in Power Query Editor.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
        Learn the Power Query M formula language in Power BI Desktop with a step-by-step data transformation pipeline: remove columns, promote headers, rename columns, filter rows, replace values, change case, and add calculated columns.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143528],"tags":[],"coauthors":[6779],"class_list":["post-80057","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80057","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=80057"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80057\/revisions"}],"predecessor-version":[{"id":109875,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80057\/revisions\/109875"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=80057"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=80057"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=80057"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=80057"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}