Five useful functions of Power Query
Hey there, I want to use this article to go through 5 useful functions of power query. Please be noted that power query is to solve your routine job, not to replace SQL for questions due to the consumption of your calculation resources. This article is for the people who have ordinary jobs of data integration, regular reports(daily, weekly, monthly), and some actions needed to do every single time.
Power query is a good choice for people who have no database access limited code skills. The first step is to open your excel and use the power query function. (please refer to the icon, my excel version is in traditional Chinese)
Step 1
Is to fix your source file name. You get your source data either from daily reports, Azure, or other sources. Remember to unify your source file name so that you can enjoy the benefit of auto-refresh.
Step 2
It is to do the data clean in the power query, and as I mentioned at the beginning, a routine job may take your time doing the same jobs. Five functions are powerful in power query.
The functions I introduced today are all under the transform function table. The first function is transposing (please refer to the icon, the first starts with the left-hand side ). This function enables you to transpose data raw and column, using when the data format from the source is different from what you want to present.
The second function is the pivot function. Like the pivot in excel, you can use the process to build a cross-view based on the pivot on attributes.
The third function is unpivoting. You may experience that when you take files from your finance/marketing team, you get beautiful pivot files without raw data, and you need to combine other data to present. Now you can use the unpivot function to change the format to a natural data style. Then you can add or join other data set for use.
The fourth function is the date-time function(you see the clock icon and the one next to it, right). In power pivot, the date-time process is super powerful and easy to use. You can change the time stamp into year/month/week, or you can extract a specific part (let’s say a month) you need. Depending on your needs, you can also split your timestamps into three columns (hh, mm, ss). You can also use the filter function to filter out the necessary granularity.
The date-time flag is essential for analysts and businesses. It affects the dimension we read the data, the judegemnet and the next step consideration.
Last but not least, the statistic function.
You can do the statistics(Sum, Max, Min, distinct count, count) on top of any filters you’ve taken).
You can connect more than one dataset and clean/ integrate your data under power query functions. All the processes you’ve made will be recorded on the function window right-hand side. You can click on each step to roll back.
Now, store and execute what you have done. And you can see the dataset has been updating as you set in the power query.
Next time you get an updated version source file (with the same file name), you can directly use the refresh function, and you can leverage what you have set in the power query and get the clean data for further use.
Again, the power query is convenient but high calculation power consumption. If you have access to the database, please use SQL to query data and clean data. Alternatively, if you cannot use the database but have a pretty large volume of routine jobs, you can consider using a power query to make your effort count.
