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 the Category column.
• The each function is used to perform calculations on each group.
• Table.Sort sorts the grouped data by the Category 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.