Business intelligence is no longer the domain of large corporations alone. Thanks to tools like Microsoft Power BI, even small and mid-sized businesses can gain powerful insights from their data. At the heart of Power BI’s data handling capabilities lies Power Query – a potent data transformation tool. This blog post aims to explore some of the advanced features of Power Query, demonstrating how you can manipulate data to fit your needs, accompanied by usable code examples.
Easier Data Handling with Power Query
Let's explore some straightforward operations you can perform with Power Query:
1. Combining Customer and Sales Data
Here's how you can merge two datasets, like a customer list and sales information, based on common customer IDs:
let
CustomerData = Table.FromRows(Json.Document(File.Contents("C:\YourData\Customers.json")), {"CustomerID", "CustomerName"}),
SalesData = Table.FromRows(Json.Document(File.Contents("C:\YourData\Sales.json")), {"SaleID", "CustomerID", "Amount"}),
CombinedData = Table.Join(CustomerData, "CustomerID", SalesData, "CustomerID", "Combined")
in
CombinedData
2. Creating a 'Customer Status' Column
Add a new column to classify customers based on their purchase amounts:
let
SalesData = Table.FromRows(Json.Document(File.Contents("C:\YourData\Sales.json")), {"CustomerID", "Amount"}),
WithStatus = Table.AddColumn(SalesData, "CustomerStatus", each if [Amount] > 500 then "Premium" else "Regular")
in
WithStatus
3. Grouping Sales by Product
Group sales data by product and calculate total sales for each:
let
SalesData = Table.FromRows(Json.Document(File.Contents("C:\YourData\ProductSales.json")), {"ProductName", "Amount"}),
GroupedData = Table.Group(SalesData, "ProductName", {"TotalSales", each List.Sum([Amount]), type number})
in
GroupedData
These examples demonstrate just a few of the powerful operations you can perform with Power Query, making it an invaluable tool for data analysis and reporting.