There are a lot of tasks for which previously you had to either terribly pervert with formulas or write macros – now they are easily and beautifully done in Power Query. This is the most useful add-on for a wide range of users among all listed in this article.
The principal feature of Power Query is that all actions for importing and transforming data are stored in the form of a query – a sequence of steps in the internal Power Query programming language, which is succinctly called “M.” Steps can always be edited and replayed any number of times (update query). The functions, no matter the way you got, there are completely identical. In Excel 2016, all the Power Query functionality is already built-in by default and is located on the Data tab as a group of Get and Transform. In the first case, a separate tab appears in Excel after installation. Power Query is found in two versions: as a separate add-in for Excel, which can be downloaded from the official Microsoft website and as part of Excel 2016. For example, perfectly replaces the VPR function (VLOOKUP) and its analogs. Substitute data from one table to another by the coincidence of one or several parameters.Transform tables in every way by bringing them into the desired view (filter, sort, change the order of columns, transpose, add totals, expand cross-tables into flat ones, and rollback).
Correct case, number-as-text, fill in the blanks, add the correct table header, disassemble the text sticking to the columns and merge back, divide the data into components, etc. Extra columns or rows, repetitions, service information in the “header,” extra spaces or unprintable characters, etc. From Excel workbooks, you can automatically download data from all sheets at once. Collect data from files of all basic data types like XLSX, TXT, CSV, JSON, HTML, both singly and in bulk, from all the files in the specified folder.Download data to Excel from almost 40 different sources, including databases like SQL, Oracle, Access, Teradata, corporate ERP-systems like SAP, Microsoft Dynamics, Internet services like Facebook, Google Analytics, almost any sites.It can do a lot of useful things for everyday work: Back in 2013, a specially created group of developers inside Microsoft released for Excel a free Power Query add-on (other names are Data Explorer, Get, and Transform). It allows users to extract data from different sources, manipulate the specified data into a form that matches their needs, and load it into Excel. Power Query is an ETL (Extract, Transform, Load) self-service tool that works like an Excel add-in. Not everyone clearly understands what is behind these concepts, how they are interrelated, and how they can help an ordinary Excel user. The terms “Power Query,” “Power Pivot,” “Power BI,” and other “Power” more often appear in articles and materials about Microsoft Excel.
0 kommentar(er)
