User interface

The first step of getting familiar with a new product is to take a quick overview of the most important features that it has to offer.

In the video below we will swipe through the UI without going into many details. The idea is to get you familiar with all the building blocks of PowerQuery.

The video is quite lengthy (possibly the lengthiest in the whole course), but it is easy to follow and you do not need to mark any notes, as you can find all the key insights in a list below the video.

Key points!

• PowerQuery exists both in Excel and PowerBI interface
• It also exists in other Microsoft products, such as Dataflows or PowerApps
• PowerQuery in Excel is available since the Excel 2010 version
• Office 365 Excel PowerQuery is the preferred version since it is updated regularly to include new features.

• Home tab
  • The most important commands in PQ,
  • Adding new data sources,
  • Data source credentials,
• Transform tab
  • Data transformations and manipulations,
  • Most of the commands can be also accessed with a right-click on a designated column/columns,
  • R/Python scripts,
• Add Columns tab
  • The most important is the General group in which you can invoke custom functions or custom columns,
• View Tab
  • Data presentation,
  • Data quality and distribution check,
  • Remember that, from this tab, you can show applied steps pane in case it disappears,
  • Query dependency view that can help you understand query interactions,
• Tools
  • Query diagnostics to check bottlenecks in case the query is loading slowly.

Users usual time distribution in each tab:

• Home – Plenty of time, you need to get acquainted with it,
• Transform – Most of the commands can also be accessed with right-clicking,
• AddColumns – Every time you will need to add an additional column into a selected query,
• View – Seldomly, most of the time to check query dependency or to bring back applied steps pane or formula bar,
• Tools – Rarely, only in case your query is extremely slow on loading.