Excel users would achieve the same task using lookup formulas. Power Query allows us to combine multiple tables of related data. Each time we update the data source, the steps are applied and the outputs are updated. This means that when reports are refreshed or changes are made, model inputs can be refreshed in seconds, with no need to re-do the whole process from scratch.Įxample: In this image, we can see the list of steps that have been applied to the dataset. If the above example isn’t enough, Power Query also remembers each of the steps that were taken to transform the data.
And importantly, the process probably took about a minute to complete. With a few simple steps, Power Query allows the user to transform the data into the below format. There are unneeded rows at the top of the file, columns of blank values, values to fill down, and dates to unpivot. This type of data will be very familiar to Excel users, who know just how much work is involved in re-formatting data to fit their analysis needs. The problem is that the data in their text file looks like this (below).
This means modifying the layout, removing errors, and generally making the data more usable for analysis.Ĭommon data transformations performed in Power Query include:Įxample: In this example, a user wants to calculate the monthly performance of each store. The core purpose of Power Query is to transform data. Common use cases are to modify the layout of data, connected to central data sources, merge related tables, or combine multiple files.Power Query remembers the user’s data transformation steps, effectively automating lengthy and manual processes.Power Query is an invaluable tool for any analyst working in Excel, saving users hours of time, reducing manual errors, and allowing users to source data from a central source of truth.Power Query is a data transformation tool available in both Microsoft Excel and Power BI.
0 kommentar(er)
