Power BI Query

The Power BI Query is a combination of connecting to different data sources, getting data or tables from those, transforming them as per the requirements, and load into the dataset. The first two steps, connecting and getting tables, can be done by the “Get Data” option in the Home tab. Then, when you click on the “Edit Queries” option, you have the chance to transform tables as per your need.

Power BI Query is a place where you have to apply all data transformations because it happens before data loads into the desktop. It include changing data type, combining data from multiple data sources, adding a new column, removing an existing column, etc.

Power Query in Power BI user interface

  1. Transformation Options: The Menu has different tabs, and by default, it opens with the Home tab with frequently used items. Each tab has other options for performing data transformations.
  2. Formula Pane: To write a new Power BI DAX query to transform data or edit/alter the existing formula.
  3. Data Preview: It displays the data (rows and columns) inside the selected table. It doesn’t display all the records, but it shows a preview to get an idea of its data.
  4. Queries Pane: List of all the tables available in a dataset. It divides the columns into dimensions, measures, and other queries (for unknown tables).
  5. Query Settings Pane: It has two options: you can change the properties of the selected column or undo/redo the applied steps to it. Because it keeps a record of each step you have taken in data transformation. So, while applying the transformation, if you have made any mistakes or want to change something, you can use this pane to remove those steps at any point.

Power BI Data Transformation Query

We don’t get the data that we can directly use in the reports in real time. Instead, we have to clean that data to meet our business standards. We have a query editor within the Desktop to perform all the needed operations. This section of the Microsoft Power BI query explains most of the data transformations you need.

  1. Bins
  2. Clusters
  3. Format Dates
  4. Groups
  5. Hierarchies
  6. Joins
  7. Pivot Table
  8. Query Groups
  9. UnPivot Table

Calculated Fields

This section of the Power BI query covers the calculated fields. Sometimes, we don’t get the columns that we need to perform visualization. In these situations, we can create calculated fields that we have shown below. Then, we can write simple arithmetic operations or the most powerful DAX functions within these columns. For complete tutorial, please refer to the Power BI tutorial article.

  1. Calculated Columns
  2. Conditional Columns
  3. Calculated Measures
  4. Calculated Tables
  5. Custom Columns