Master Power Query: 5 Essential Transforms for Power BI

If you're working with Power BI, you may have noticed that there are many ways to perform tasks in both the Power Query Editor and Power BI Desktop. In this video, Justin Vogel from Pragmatic Works shares his top five transformations in Power Query, designed to streamline your workflow and improve data modeling. The key theme is planning ahead to prevent errors and save time.

Tip 1: Prepare to Append

• Appending is the process of stacking one table on top of another, but this can create issues when tables have different column structures.
• In this example, the US sales and international sales tables have similar structures, but the international table has an extra 'country' column.
• To prepare for appending, Justin shows how to create a conditional column in the US sales data to add a 'country' column, matching the international sales data.
• This proactive step prevents null values in the resulting appended table and ensures that the columns align correctly.

Tip 2: Use the Go-To Column

• Located under the 'View' tab, the 'Go To Column' feature is useful for quickly navigating and analyzing large datasets.
• It allows you to search for specific terms (like 'date' or 'key') and helps identify relevant columns for your data modeling.
• By typing keywords in the search bar, you can easily find columns related to dates, keys, or any other specific criteria relevant to your model.

Tip 3: Choose Columns for Data Modeling

• The 'Choose Columns' button, found in the Home tab, is vital for selecting or deselecting columns during the data modeling phase.
• For example, selecting relevant columns for a 'product' table or 'geography' table helps visualize the eventual star schema.
• Using this feature, Justin demonstrates how to build out a product table by keeping only the essential columns such as 'product ID', 'category', and 'manufacturer'.

Tip 4: Check Data Types

• Proper data types are crucial for effective filtering and relationships within your data model.
• For example, date columns should be set as 'date' data type, while 'year' columns should be set as whole numbers to enable effective filtering.
• Adjusting data types prevents potential issues like losing leading zeros in ZIP codes when switching from numbers to text.

Tip 5: Use Column from Examples

• The 'Column from Examples' feature is a powerful tool that allows users to clean or transform data based on examples they provide.
• In Justin's example, he uses this feature to split combined columns like 'city, state, country' into individual components, ensuring clean and accurate data.
• By typing in one example of the desired output, Power BI automatically applies the transformation to the entire column.

Conclusion

In this video, Justin Vogel demonstrates how to think like Power BI to improve data transformation and modeling in the Power Query Editor. By planning ahead and using these five essential transforms—prepare to append, use the go-to column, choose columns, check data types, and use column from examples—you can create more effective data models with fewer errors. Keep these tips in mind to streamline your Power BI workflow, and don't forget to check out Pragmatic Works' Power BI training courses for deeper learning.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.