Project Botticelli

Transforming Data with the Query Editor Purchase this course

17 November 2017 · 261 views

Power BI Course

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 below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

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

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Online Courses