Common Power Query data transformations using M-Query

M is the programming language used in Power Query to perform data transformations. Here are some examples of M code that you might find useful for data transformation.

1. Table.SelectRows: This function allows you to select rows from a table based on a condition.
   For example, you could use Table.SelectRows(#”MyTable”, each [Column1] > 100) to select all rows where the value in Column1 is greater than 100
2. Table.Filter: This function is similar to Table.SelectRows, but it allows you to specify multiple conditions using an AND or OR operator.
   For example, you could use Table.Filter(#”MyTable”, each [Column1] > 100 and [Column2] = “Yes”) to select all rows where the value in Column1 is greater than 100 AND the value in Column2 is “Yes”.
3. Table.Sort: This function allows you to sort the rows in a table based on one or more columns.
   For example, you could use Table.Sort(#”MyTable”,{{“Column1”, Order.Descending}}) to sort the rows in MyTable by Column1 in descending order.
4. Table.TransformColumns: This function allows you to apply a transformation to one or more columns in a table.
   For example, you could use Table.TransformColumns(#”MyTable”, {{“Column1”, each Text.Upper, type text}}) to convert all the values in Column1 to uppercase.
5. Table.Distinct: This function allows you to remove duplicate rows from a table.
   For example, you could use Table.Distinct(#”MyTable”) to remove all duplicate rows from MyTable.
6. Table.Pivot: This function allows you to pivot the data in a table, turning unique values in a column into separate columns.
   For example, you could use Table.Pivot(#”MyTable”, List.Distinct(#”MyTable”[Column1]), “Column1”, “Column2”) to pivot the data in MyTable, creating a new column for each unique value in Column1 and filling in the values from Column2.
7. Table.Unpivot: This function allows you to unpivot the data in a table, turning columns into rows.
   For example, you could use Table.Unpivot(#”MyTable”, {“Column1”, “Column2”}, “Attribute”, “Value”) to unpivot the data in MyTable, creating a new column called Attribute that contains the name of the original columns, and a new column called Value that contains the corresponding values.
8. Table.RenameColumns: This function allows you to rename the columns in a table.
   For example, you could use Table.RenameColumns(#”MyTable”,{{“Column1”, “NewColumnName”}}) to rename Column1 to NewColumnName.
9. Table.AddColumn: This function allows you to add a new column to a table based on an expression.
   For example, you could use Table.AddColumn(#”MyTable”, “NewColumn”, each [Column1] + [Column2]) to add a new column called NewColumn that is the sum of Column1 and Column2.
10. Table.Group: This function allows you to group the rows in a table based on one or more columns, and apply an aggregation function to the other columns.
    For example, you could use Table.Group(#”MyTable”, {“Column1”}, {{“Total”, each List.Sum([Column2]), type number}}) to group the rows in MyTable by Column1 and create a new column called Total that is the sum of Column2 for each group.