Getting the data right requires 80% of the effort in any analysis. Wisdom says that an analyst should spend time with data: cleaning, filtering, merging, adding categories, and more. If the data set is right and complete, the job of the analyst becomes simpler.
Power Query not only offers a no-code solution for most of the challenges, but it also helps in automating the most mundane tasks related to data transformations.
This article highlights five such features that any data analysts or data scientists should find handy. These techniques can significantly reduce the effort
The benefit of Power Query is that it is easy to learn and the benefits it has is significantly high over traditional Excel-based formulas and VBA
In analytics, qualitative data plays an important part, especially when it defines the data as dimensions. And because Power Query is case sensitive, Text transformations are crucial. A few commonly used Text transformations are:
Splitting and merging the columns
Formatting texts (Upper- & lower-case transformations)
Removing & Keeping Duplicates
Extract certain characters
Working with Text transformations in Power Query
Power Query offers many options on date transformations. Creating a calendar table in the Power BI data models is highly recommended. We can use Date transformations for creating a calendar table in Power Query as well. And someone who is not keen about including a separate calendar table, Date transformations comes handy in extracting:
· Year from a date
· Quarter from a date
· Month name or Start of the month from a date
· Start or End of the week from a date
· Difference between two dates (including today)
Date transformations with Power Query
Lesser, the better. In any data model, a lesser number of tables we have, more convenient it is to optimize the model and write DAX queries. Hence, the objective is to create flat tables that consist of columns from multiple tables. It requires running JOINS using SQL queries or VLOOKUP/XLOOKUP in Excel worksheets. Power Query offers a more straightforward option in the form of Merge Queries.
Unpivot & Pivot Columns
Not always we get data in the right structure. Pivot & Unpivot columns feature in Power Query is convenient in transforming the data table in the proper format. It helps in adding flexibility in the data model. For example, the table below is suitable for presenting the data, but not helpful in calculations such as Year to date, Quarter to date, month-over-month variation.
But following data table can simplify the required calculations.
Pivot & Unpivot helps in quickly transforming the data in the desired structure.
Unpivot & Pivot transformations with Power Query
One of the standard requirements of an analyst is to add dimensions to the data model. We transform the quantitative data into qualitative data. This type of transformations provides additional perspectives during analysis.
For example, we create data buckets according to age group such as less than 25, 25 to 35 years,35 to 45 years. Creating such data buckets requires a decent comfort level with IF THEN ELSE logic in any platform.
Power Query offers a no-code solution for such scenarios.
Adding categories with Power Query