top of page

New - original Computer courses

Power Quwery , Power Pivot, Power View - the golden age and swan song of Excel

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.

Power Pivot is also an add-in for Microsoft Excel, but designed for slightly different tasks. If Power Query is focused on importing and processing, then Power Pivot is needed mainly for complex analysis of large amounts of data. As a first approximation, you can think of Power Pivot as pumped pivot tables.

The general principles of working in Power Pivot are as follows:

  1. First we load data into Power Pivot - 15 different  sources are supported: Common DBs (SQL), Excel, Access... , text files, data feeds. In addition, you can use Power Query as a data source, which makes the analysis almost omnivorous.

  2. Then between the loaded tables links are configured_or, as they say, a Data Model is created. This will allow in the future to build reports on any fields from existing tables as if it were one table. And no VPR again.

  3. If necessary, additional calculations are added to the Data Model using calculated columns (similar to a column with formulas in the "smart table") and_cc781905-5cde-3194-bbc3b-136d_mer -3194-bb3b-136bad5cf58d_ (similar to the calculated field in the summary). All this is written in a special Power Pivot internal language called DAX (Data Analysis eXpressions).

  4. On the Excel sheet, according to the Data Model, reports of interest to us are built in the form pivot tables and charts.

Power View - This add-in appeared for the first time in Excel 2013 and is designed to "revive" your data - building interactive graphs, charts, maps and tables. Иногда для этого используют термины дашборд (dashboard) или панель показателей  (scorecard). The bottom line is that you can insert a special sheet without cells into your Excel file - a Power View slide, where you can add text, pictures and a lot of different types of visualizations based on your data from the Power Pivot Data Model.

Unfortunately, Power Pivot is not yet included in all versions of Excel. If you have Excel 2010, then you can download it for free  from the Microsoft website . But if you have Excel 2013-2016, then it all depends on your license, because. some options have it enabled (Office Pro Plus, for example) and some don't (Office 365 Home, Office 365 Personal, etc.)

What is Power BI? - It's not Excel

Power BI - This is the future of Excel

Power BI -

is a business intelligence tool that allows you to visualize data and share insights with colleagues or embed data in an app or website. Connect to hundreds of data sources and bring your data to life with dynamic dashboards and reports.

View your data in new ways
Make informed decisions quickly. Load, model, and explore your data with visual reports, then publish, share, and collaborate. Power BI integrates with other tools, including Microsoft Excel, so you can get started quickly and seamlessly use the service alongside your existing solutions.

Connect to your data, wherever it is. Then explore your data with stunning interactive visualizations.  Publish reports and dashboards, collaborate with your team, and share insights within and outside your organization. Take action with consistent access to actionable information from your computer or mobile device with Power BI apps built by Microsoft, our partners, and your organization.

bottom of page