Power Query allows you to retrieve, extract, and transform (ETL) data prior to bringing it into Excel (and/or into a Power Pivot data model). It allows you to insert & remove columns, filter and sort tables, and change data types.
It runs on the “M” Language (short for Mashup) and is labelled as “Get and Transform,” in the “Data” Ribbon in Excel, and is an optional tool. When importing data into Power Pivot can import it directly into Power Pivot, or you can channel it first through Power Query if your data is noisy.
Also known as the DAX Engine (Data Analysis Expression), Power Pivot is an Excel feature that allows you to build a data model with relationships, create calculated columns and measures.
Power Pivot allows Excel to import larger data sets from a variety of sources and create more sophisticated data models using DAX.
Power Pivot allows you to create visualizations using Power View and Power Map. Power Maps is a geographic visualization tool, while Power View allows you to present data in interactive dashboards.
Power Pivot is a free add-on for Excel 2010 and Excel 2013. From Office 2016 onwards it can be found in Excel.
Power BI works on top of the Power Pivot and Power Query engines. You can shape and analyse your data either using Get Data in Power Query or Data Model in Power Pivot. You can then Power BI reports and interactive dashboards using Power BI’s wide variety of data visualisations.