Tecflix

Transforming Data with the Query Editor Purchase the entire course

21 December 2018 · · 309 views

Power BI Course

Transforming Data with the Query Editor

Once you have connected to your data sources you can use the functionality of the Query Editor to transform it so that it is in the format you need it for Power BI - which is typically a dimensional model. There are a number of common tasks you’ll need to accomplish: removing unnecessary columns; renaming columns to use human-readable names, making sure you use the correct business terminology; setting the correct data types on columns; filtering the data to remove unnecessary rows; aggregating or summarising the data; and pivoting or unpivoting data. There are a lot of options for manipulating and transforming data in the Query Editor. While it may look like some of the same options are available on different tabs in the ribbon, the options on the Transform tab replace existing values with new ones while those on the Add Columns tab leave existing data in place and add new columns to your table.

Handling errors is also a very important task at this stage. Error values are typically encountered when data in a column coming from a data source does not match the data type set for that column in the Query Editor.

There are two ways to combine data from multiple queries into a single query: merging and appending. Appending is similar to a SQL union operation, and involves data from one query being added onto the bottom of data from another. Merging is similar to a SQL join between two tables, or a VLOOKUP in Excel. You can create a copy of a query by duplicating it; referencing a query involves using the output of one query as the input to another. Parameters can be used to create something like a global variable, where a value like a file path can be shared easily between multiple queries.

Log in or purchase access to play the video.

  • Power BI New Features and Course Updates 1-min Free—Watch Now

  • What is Power BI? 59-min Watch with Free Subscription

  • Licensing 8-min

  • Connecting to Data Sources 42-min

  • Connecting to Data Sources (2018 Updates) 23-min

  • Data Import Concepts 55-min

  • Transforming Data with the Query Editor 1-hour 20-min

  • Transforming Data with the Query Editor (2018 Updates) 13-min

  • Advanced Data Loading 49-min

  • Modelling Data 1-hour 10-min

  • Modelling Data (2018 Updates) 14-min

  • DAX and the Data Model 31-min

  • DAX and the Data Model (2018 Updates) 10-min

  • Power BI Desktop vs Excel 37-min

  • Cloud and Desktop Power BI Dashboards and Reports 45-min

  • Power BI Reports (2018 Updates) 40-min

  • Building Reports in Excel 32-min

  • Sharing, Distribution, and Security 42-min

  • Data Refresh 8-min Free—Watch Now

  • Administration and Auditing 11-min Watch with Free Subscription

  • Azure Analysis Services 1-hour 3-min

  • Power BI Premium and Power BI Embedded 36-min Watch with Free Subscription

Purchase a Full Access Subscription

 
Individual Subscription

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase
 
  • You can also redeem a prepaid code.
  • Payments are instant and you will receive a tax invoice straight away.
  • We offer sales quotes/pro-forma invoices, and we accept purchase orders and bank transfers.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.
  • See pricing FAQ for more detail.
In collaboration with
Project Botticelli logo Oxford Computer Training logo SQLBI logo Prodata logo