INFORMBURO

Power Query is a data connection technology that allows you to discover, connect, combine, and refine data from multiple sources for further analysis. Power Query functions are available in desktop versions of Excel and Power BI.

When using Power Query, you often need to follow a few standard steps. (for example, remove a column, change the data type, or merge tables) to suit your needs. Once the data is generated, you can share your findings or use a query to generate reports.  

Back in 2013, a specially created group of developers within Microsoft released a free add-on for Excel Power Query  (other names - Data Explorer, Get & Transform), which can do a lot of useful things for everyday work :

• Load data into Excel from almost 40 different sources, including databases (SQL, Oracle, Access, Teradata...), corporate ERP systems (SAP, Microsoft Dynamics, 1C...), Internet services (Facebook, Google Analytics, almost any sites).

• Collect data from files of all major data types (XLSX, TXT, CSV, JSON, HTML, XML...), both individually and in bulk - from all files specified folder. From Excel workbooks, you can automatically download data from all sheets at once.

• Clean received data from "garbage": extra columns or rows, repetitions, service information in the "header", extra spaces or non-printable characters, etc.

• Bring data to  order: correct case, numbers-as-text, fill in spaces, add the correct table "header", parse "sticky" text into columns and glue back, divide the date into components, etc. .

• In every possible way transform tables, bringing them into the desired form (filter, sort, change the order of columns, transpose, add totals, expand cross tables to flat and collapse back).

• Substitute data from one table to another by matching one or more parameters, i.e. perfectly replaces the function VLOOKUP(VLOOKUP) and its analogues.