PQB01: Introduction to Power Query

The first episode will help you answer the following questions:

Why Power Query?
Where we can find it in excel?
How Power Query Editor works?

PQB02: Extract data from the current excel workbook

The second episode demonstrates how to extract data from the current excel workbook to the power query then create a dynamic unique list of values. Data in current workbook could be in 3 different forms: 1) Normal cell range 2) Named range and 3) Excel table format.

As query helps you to automate your recurring work, you will see how you update your query with a single click once new data arrived to your workbook

PQB03: Consolidate data extracted from multiple worksheets

The third episode demonstrates how to consolidate data extracted from multiple worksheets stored in another excel workbook using power query.

The example shows how to consolidate an OPEX budget of various accounts and cost centers into a single pivot table report. Critical data transformation actions presented in the video, such as using sheet name as new column and unpivot amount columns

Once you update/add new accounts or cost centers, final report can be updated with a single click.

PQB04: Extract data from a folder contains CSV/TXT files

Although the example is showing how to deal with a folder contains CSV files, same steps apply on TXT files as well. The M code function "Csv.Document" can handle both format. The video also discusses multiple data transformation actions including: Split columns, use filename as a new column, Merge columns to form a full date column considering your regional settings and, load query to a pivot table and create a refreshable sales report

PQB04.5: Extract data from a folder contains Excel Workbooks

This video demonstrates how to combine data from a folder containing Excel Files using Power Query, then perform and automate required data transformation, when you add more files to the folder, automatically flows to your final report.

Method 1: Use the "Combine Files" button
Method 2: Use "Excel.Workbook" function

PQB05: Extract data from an Access Database & load to data model

Let Power Query, Data Model & a pivot table handle 3m+ rows of data stored in Access Database

Extract sales transactions from an access database
Expand columns to retrieve data from related tables in the database
Add custom column to calculate the sales revenue
Load query to the data model to speedup your reporting process & add DAX measures
Create a pivot table based on Data Model to generate your report

PQB05.5: Power Query in Power BI

This episode is a complete introduction to Power BI Desktop in less than 30 minutes. By the end of the video you will have the confidence that once you master Power Query and Data Model in excel, it becomes easy to move your work to Power BI if required.

Using the example presented in PQB05, you will see how to connect to data stored in an Access Database using Power BI desktop, then perform all transformation and calculations required for analysis. At the end of the video you will see how easy to create an interactive dashboard in Power BI using multiple visuals such as: Maps, Tables, Barcharts & slicers.

PQB06: Connect to the web with Power Query

In this episode, you will see how to connect to data stored on a website using power query. In a simple but practical example, the video shows how to use power query to connect to https://finance.yahoo.com and get Google’s historical share prices. Multiple data transformations presented including : Add column to extract month name and re-arrange columns. Once all transformation completed, you can load the query to a table & use the amazing “Stock Charts” and a slicer to present the output. The WoW part of the story, when the website updates, you can refresh the query to get the updated stock prices. To scale up your work, copy the query then change the source and get other shares historical prices in few seconds

PQB07: Transform & Organize Chaotic Data

Issue: All invoices data came in one column (Invoice Number, Amount and Month), moreover, number of months/invoices per customer is variable.

Required: Split invoice data in columns with consistent data types, then report customer invoices using a pivot table.

In order to transform & organize the input data, labels must be separated from values, using the following feature: Split COLUMN by delimiter into ROWS, Split column by digit to non-digit, Split column by non-digit to digit, and finally Split column by number of characters.

The output report is automated, add more invoices, update the report with "One Click”

PQB08: Data Transformation - Append data from 3 different tables

Append is one of the vital power query skill you need to utilize power query capabilities. Having 3 years sales in 3 separate tables with inconsistent number of columns, you need to unify columns, and append all data in a new single table. In the video, you will see how to create a query as "Only Connection" for each sales table, then append all in a new query. After transforming an loading the final query to an Excel Table, updates in original data will reflect to the final output automatically once you refresh it.

PQB09: Perform VLOOKUP in Power Query using Merge Queries

Here is another essential power query skill, how to link data between 2 tables, like VLOOKUP, Merge Queries is the easiest way to lookup data in the 2nd table using information in a common column. Power Query provides many types of Join kinds while performing merge, the most famous and the one simulating VLOOKUP is the Left Outer join kind. In continuation of the example presented the previous episode (PQB08) this episode will demonstrate how to search and find Product & Category names from the Product Lookup Table in order to report sales by Product and Category.

PQB10: Use Merge Queries to compare between 2 tables

ِAfter knowing the Left Outer join kind well (explained in PQB09) you get to know how to use other join kinds such as: Full Outer, Inner, Left Anti, and Right Anti. The application of such Join Kinds, will enable you to perform a dynamic comparison between table to identify: all values from 2 tables, intersection between 2 tables, values in 1st table only and values in 2nd table only

PQB11: Solve duplicate issue while performing Merge Left Outer

While performing Merge Queries with Left Outer join kind sometimes it results into duplications of some lines in the output table, this could shake your faith in the whole merge functionality.

In this video you will see what is the reason? and how to solve this issue if happened?

PQB12: Merge - replace SUMIF with Power Query

Is it possible to perform SumIf in Power ََQuery?

With a tiny tweak you can use Merge Queries to simulate SumIf. The video will explain how the direction of the merge will decide whither you are simulating VLOOKUP or SUMIF.

PQB13: 2 Dimension search using Formulas & Power Query

In a comparison between Excel Sheet Formula and Power Query, you will see how to perform 2 Dimension search (search by in table by 2 criteria).

Excel sheet formula: Vlookup + Match
Power Query: Unpivot Columns + Merge (left outer)

PQB14: Group and Summarize data using GROUP BY

In the example: 3 tables containing sales data for the first 3 weeks from January

Required: Collect data in one table using Power Query (Append using Excel.CurrentWorkbook), select the data required for the report and exclude the unwanted. Then summarize data using “Group By” and finally prepare a dashboard to present daily sales. update dashboard automatically when week 4 & 5 added

PQB15: Dynamic Filter using Power Query, enter criteria from Excel

In a case that you are working with a table contains millions of rows and you want to view a subset of the data inside your excel worksheet, you may utilize Power Query to build a dynamic filter based on a criteria entered from the excel worksheet itself.

Following power query skills will be explained: 1) Use Excel.CurrentWorkbook to extract and append data, then create a dynamic unique list using a duplicate of the query. 2) Convert a query into a single (primitive) value using “Drill Down” and use it as a filter criteria

Inside the excel worksheet, the following will be explained: 1) Use data validation to create a drop-down menu. 2) Add a refresh button, and link it to a recorded macro

PQB16: Manage Queries: Duplicate, Reference or Append

What is the difference between Duplicate & Reference? Any relation with Append?

An example of 3 years sales data in 3 different excel workbooks, will help us to answer the above questions. The requirement is to report the yearly sales in a number of different ways, the application of Duplicate, Reference or Append will be based on the situation and based on the report required

Advance to the Next Level

Home