Microsoft Power BI Query Editor and DAX Programming

In this workbook/guide, we will cover many behind-the-scenes capabilities of Power BI. We will use the Power BI Desktop tool to get data, the Power Query Editor to filter the data source, and use the DAX programming language to adjust the results.

Get Data - The first step in the Visual Report development process is to Get Data. Databases are becoming very large and now contain more records than what is necessary to build Reports. We will cover the Get Data process from different data sources and show various ways to extract data including Excel, CSV, XML, Text, SQL Server, and Access.

Query Editor - This is an interface used to filter undesirable records, correct many database glitches, clean up data, fix anomalies, and transform, or shape data source information. It can also be used to fix misspellings, fix unclear labels, columns containing the wrong data types, automatically merge two data sources into a single table, adjust columns(fields), and rows (records), split columns, combine columns, replace information, transpose an entire table, and sort columns.

DAX Programming - This will allow you to create new columns that are not already contained in a database. In order to accomplish this, the DAX (Dynamic Analysis Expressions) programming language can be used to enhance records and reports. DAX contains most of the Excel functions, but it also has a built-in record-filtering capability to pull data based on specific criteria. If a user applies a filter in Power BI, it might skew the results. However, DAX will allow you to extract records and ignore any locally applied filter. The workbook explains DAX formula terms, basic structure, and common development techniques. Also included, is how to create New Columns in a table, custom calculations known as Measures, as well as creating New Tables.