Excel Power Query Code

Power Query is a powerful tool in Excel that allows you to transform, clean, and load data efficiently. It provides a user-friendly interface and a simple programming language called M (Power Query Formula Language) to automate data manipulation tasks. In this blog post, we will explore some useful Power Query code examples and techniques to enhance your data analysis and reporting capabilities.
Loading and Transforming Data

Power Query is excellent for loading and transforming data from various sources. Here's how you can load data from a CSV file and perform some basic transformations:
let
Source = Csv.Document(File.Contents("C:\Path\To\Your\File.csv"), [Delimiter=",", Encoding=1252, Quote=Regex.Replace(CodePage(1252), "[", "", "i"), ColumnTypes="Inferred"]),
#"Removed Columns" = Table.RemoveColumns(Source, {"Column1", "Column2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column3", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{#"Column4", "NewColumn4"}})
in
#"Renamed Columns"
In the above code:
- The
Csv.Document
function reads data from a CSV file. - We use the
Table.RemoveColumns
function to remove unwanted columns. Table.TransformColumnTypes
is used to change the data type of a specific column.- Finally,
Table.RenameColumns
renames a column for better clarity.
Data Cleaning and Standardization

Power Query offers various functions to clean and standardize your data. Here's an example of how to handle missing values and replace them with a default value:
let
Source = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
#"Replaced Values" = Table.ReplaceValue(Source, Text.Combine({blnEmpty, blnNull}), "Default Value", Replacer.ReplaceValue, {"Column1"}),
#"Removed Errors" = Table.RemoveNulls(#"Replaced Values", {"Column1"})
in
#"Removed Errors"
In this code:
Table.ReplaceValue
replaces missing values with a default value.Table.RemoveNulls
removes rows with null values from the specified column.
Data Aggregation and Calculations

Power Query can perform complex calculations and aggregations on your data. Let's see an example of calculating the total sales for each product category:
let
Source = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
#"Grouped Data" = Table.Group(Source, {"Category"}, {{"Total Sales", each [Sales] [Quantity] * [Price], type number}}),
#"Sorted Data" = Table.Sort(#"Grouped Data", {{#"Category", Order.Ascending}})
in
#"Sorted Data"
In this code:
- We use
Table.Group
to group data by theCategory
column. - The
each
function is used to perform calculations on each group. Table.Sort
sorts the grouped data by theCategory
column in ascending order.
Date and Time Functions

Power Query provides a wide range of date and time functions for data manipulation. Here's an example of extracting the year from a date column:
let
Source = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
#"Extracted Year" = Table.AddColumn(Source, "Year", each Date.Year([DateColumn]))
in
#"Extracted Year"
In this code, Date.Year
extracts the year from the DateColumn
and adds it as a new column.
Conditional Logic and Filtering

Power Query allows you to apply conditional logic and filter your data based on specific conditions. Here's an example of filtering rows based on a condition:
let
Source = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each [Column1] > 100)
in
#"Filtered Rows"
In this code, Table.SelectRows
filters rows where the value in the Column1
is greater than 100.
Combining and Merging Data

Power Query makes it easy to combine and merge data from multiple sources. Let's look at an example of merging two tables based on a common column:
let
Source1 = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Sheet2"]}[Content],
#"Merged Data" = Table.NestedJoin(Source1, {"CommonColumn"}, Source2, {"CommonColumn"}, {"Column2"}, JoinKind.LeftOuter)
in
#"Merged Data"
In this code, Table.NestedJoin
merges Source1
and Source2
based on the CommonColumn
, adding the Column2
from Source2
to the result.
Unpivoting and Pivoting Data

Power Query can unpivot and pivot data to transform it into a format that's easier to analyze. Here's an example of unpivoting data:
let
Source = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
#"Unpivoted Data" = Table.UnpivotOtherColumns(Source, "Attribute", "Value")
in
#"Unpivoted Data"
In this code, Table.UnpivotOtherColumns
unpivots the data, treating all columns except Attribute
as values.
Notes

⚠️ Note: Always ensure that your data sources are properly formatted and structured before applying Power Query transformations.
🌐 Note: Power Query's M language is case-sensitive, so be mindful of your variable and function names.
Conclusion

Power Query is a versatile tool that empowers Excel users to perform complex data transformations with ease. By leveraging the code examples and techniques discussed in this blog post, you can unlock the full potential of Power Query to streamline your data analysis and reporting processes. Whether you're loading, cleaning, or aggregating data, Power Query provides a user-friendly and efficient solution. So, dive into the world of Power Query and take your data manipulation skills to new heights!
Frequently Asked Questions

Can I use Power Query with large datasets?

+
Yes, Power Query is designed to handle large datasets efficiently. It can process and transform data quickly, making it suitable for working with extensive datasets.
How can I learn more about Power Query’s M language?

+
Microsoft provides extensive documentation and resources to learn M language. You can explore the official documentation, watch tutorials, and practice with sample queries to enhance your M language skills.
Is Power Query available in all versions of Excel?

+
Power Query is available in Excel 2016 and later versions. If you have an older version of Excel, you can install the Power Query add-in to gain access to its powerful features.