Power query (Get and transform): A new tool for Excel data consolidation

In the version of Excel 2016, a feature called The Get and transform group is built under the Data tab. It seems that some of the simple functions of commands, really apply, but Shia.

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/83/12/wKioL1dqLwringn_AACKWMfDd84860.png-wh_500x0-wm_3 -wmp_4-s_2393894457.png "title=" Pq1111.png "alt=" Wkiol1dqlwringn_aackwmfdd84860.png-wh_50 "/>

This is called "Get and Transform" group, is actually power query, what is Power query, do not know their own to Baidu. Back to this function. Why should I strongly recommend this "get and convert" feature. Because it's too powerful. You can even open a research direction in Excel. So what kind of function is this? From the literal meaning of understanding, acquisition: is to get the data, it provides almost all possible, can import interface. It is better to use the interface intelligence than the various imported data provided by Excel.

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/83/13/wKiom1dqMCGiw08nAACIEmSCyu0695.png-wh_500x0-wm_3 -wmp_4-s_1899082948.png "title=" 2.png "alt=" wkiom1dqmcgiw08naaciemscyu0695.png-wh_50 "/>

Again, it's the second word "transform", after the data is obtained, it should be a summary of data analysis, etc., but many times, the data obtained is not standardized. So then we have to sort out the data, so it's called conversion. When it comes to this conversion, it's awesome. This transformation is like doing the underlying operations on an Excel worksheet as a data collation, but the difference is. Power query records each step and can fix the previous steps at any time.

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/83/12/wKioL1dqMdGjUcOMAAEdMDXnzPk118.png-wh_500x0-wm_3 -wmp_4-s_24338815.png "title=" 3.png "alt=" Wkiol1dqmdgjucomaaedmdxnzpk118.png-wh_50 "/>

So what is the purpose of preserving this procedure? This is where the operational advantage is compared to normal Excel. When the data is updated, the Excel interface has to follow the previous steps once, and if it is done in power query. You just need to refresh it. Because power query will run again as of the previous step, it will be OK. Sort of like a macro in Excel record ha ^_^.

[Zeng log]power Query Combat video tutorial

of course, power query is a powerful data processing tool, in which every step of the operation, in fact, there are all related code generated. We call the M code, then the power query is called the M language. If you want to really, thoroughly and fully understand power query, you have to learn the M language. There's a chance in the back. Will come up with a set of principles on the power of the query point drops. Previous [ Zeng]power query actual video tutorial can only be considered as a parrot. It's like you're going to learn Excel VBA, but you have to learn the basics of Excel first. If you don't want to learn more, the basics will work for you for life.

Power query (Get and transform): A new tool for Excel data consolidation