Power Query Power BI: A Comprehensive Guide 101

on BI, BI Tool, ETL Tutorials, Microsoft, Power BI, Power Query • March 21st, 2022 • Write for Hevo

You can connect to the world of data with Power BI Desktop, generate compelling and foundational reports, and share your efforts with others – who can then build on your work and expand their business intelligence efforts.

Power Query is a Data Connectivity and Data Preparation Tool that allows users to import and reshape data from a variety of Microsoft products, including Excel, Power BI, Analysis Services, Dataverse, and others.

In this post, you’ll also find information on connecting to data, influencing data, and getting started with Power Query Power BI.

Table of Contents

Prerequisites

  • Power BI Desktop Application.

What is Power BI?

Power Query Power BI = Power BI logo
Image Source

Power BI is a Microsoft Power Platform proprietary Data Visualization and Business Intelligence platform. It is one of the most extensively used business tools for evaluating and reporting on company data. Power BI links to data smoothly, providing Immersive Visuals, Interactive Reports, and Insights through a number of built-in software services, apps, and connectors.

Power BI allows users to Aggregate, Analyze, Visualize, and Share data. It is a SaaS-based platform featuring desktop, mobile, and on-premise server options. Connecting to a range of data source connectors in Power BI allows you to import data straight from the Data Source and Visualize data stored in Databases or Data Warehouses.

Microsoft Power BI is available on desktop and mobile devices, as well as in the cloud, allowing your teams to collect, manage, and analyze data from any location. Power BI allows you to import data from a variety of sources, including Excel, CSV, SQL Server, MySQL databases, PDF, Access, XML, JSON, and many more.

Key Features of Power BI

The following are some of Power BI’s most notable features.

  • Hybridization: Power BI interfaces with a wide range of connectors, allowing users to connect to a wide range of data sources.
  • Point of View on Modeling: Power BI users may break and slice large data models into smaller bits, create distinctive diagrams, and select multiple options.
  • Assist from AI: Users of Power BI Data Analytics may utilize AI to prepare data, develop Machine Learning models, and quickly uncover insights from organized and unstructured data.
  • Important Points to Remember: Power BI simplifies the creation of data subsets and the application of Data Analytics to that data.
  • Report Dissemination: Users can easily share their reports with other members of their firm without worrying about data security.

What is Power Query in Power BI?

Power Query Power BI - Power Query logo
Image Source

Power Query is a Data Transformation and Data Preparation tool included with Microsoft Excel and Microsoft Power BI. Power Query streamlines the process of importing data from a variety of file types including Excel tables, CSV files, Database tables, Webpages, and so on, and allows you to transform your data into the proper shape and condition for better analysis.

Once you’ve configured your Power Query procedures, you won’t have to repeat the same steps with new data. You can quickly set up and automate the same data transformation procedures and produce the same data outputs with Power Query.

Microsoft Power BI’s Power Query feature allows you to do sophisticated data transformations such as:

  • Removing any extraneous columns, rows, or blanks
  • Conversions of data types – text, numbers, and dates
  • Columns can be split or merged.
  • New computed columns are being added.
  • Data aggregation or summarization, among other things.

Key Features of Power Query

The following are some of Power Query’s most notable features.

  • Connectivity to a variety of Data Sources: Power Query has been designed to export data from a variety of sources, including but not limited to text files, Excel Workbooks, and CSV files.
  • Combining Tables: The Merge option in Power Query replaces Excel’s VLOOKUP function. The latter is a handy method for looking up corresponding values, but it gets slightly problematic when applied to a huge dataset with thousands of rows.
  • Tables for Combining: When modifications to the same source data must be imported at regular periods, say, every week or month, manually ensuring data replication becomes onerous.
  • Make Your Own Functions: Power Query was created in such a way that you do not need to know how to code to utilize it. It is really simple to use because you simply click buttons and apply filters as you would in Excel.
  • Power Query keeps track of your actions and automates processes: Power Query not only makes all of these activities easier but also logs your steps so you don’t have to repeat them. If you prepare the same data every day, week, or month, it will save you a lot of time.

Simplify the Power BI Visualization Process with Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ Data Sources (including 40+ Free Sources) such as Power BI to a Data Warehouse/Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. In addition to the 100+ data sources, Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector.

Get Started with Hevo for Free

Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line. 

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Connectors: Hevo supports 100+ Integrations to SaaS platforms, Files, Databases, BI tools such as Tableau, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; Databricks (Connector Live Soon!); MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.  
  • Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (Including 40+ Free Sources) such as Tableau that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

Power Query Power BI Overview

You’ll also find information on connecting to data, influencing data, and getting started. Let’s look at the Power Query Editor.

1) Power Query Power BI: Power Query Editor

To access Power Query Editor, go to the Home tab of Power BI Desktop and click Transform data.

Power Query Power BI - Home Tab
Image Source

When there are no data connections, Power Query Editor displays as a blank window, ready to be filled with data.

The Power Query Editor window becomes more intriguing as a query is loaded. When we connect to the following Web data source, Power Query Editor loads data information, which you can then shape:

Here’s how Power Query Editor looks once you’ve made a data connection:

  • Many buttons in the ribbon are now active to interact with the data in the query.
  • Queries are listed in the left pane and can be selected, viewed, and shaped.
  • The data from the selected query is shown and ready for shaping in the center pane.
  • The Query Settings window displays, listing the query’s properties and steps that have been applied.
Power Query Power BI - Home Tab
Image Source

We’ll look at the ribbon, the Queries pane, the Data view, and the Query Settings pane in turn.

2) Power Query Power BI: The Query Tab

Power Query Editor’s ribbon is divided into four Tabs: Home, Transform, Add Column, View, Tools, and Help.

The common query tasks are located on the Home tab.

Power Query Power BI - Power BI Home Tab
Image Source

Select New Source to connect to data and begin the query-building process. A menu with the most frequent data sources is shown.

The Transform tab gives you access to popular data transformation activities like:

  1. Columns can be added or removed.
  2. Modifying data types
  3. Column division
  4. Other data-driven tasks include
Power Query Power BI - Transform Tab
Image Source

Many of the functions available from the ribbon can also be accessed by right-clicking a column or other data in the center pane.

3) Power Query Power BI: The Inquiries Pane on the Left

The left pane, or Inquiries Pane, shows the number of ongoing queries as well as the query name. When you select a query from the left pane, the data for that query is displayed in the center pane, where you may shape and change it to match your needs. The image below depicts the left pane with a query.

Power Query Power BI - Left pane
Image Source

4) Power Query Power BI: The Data Pane in the Center

The Data from the selected query is displayed in the center pane, also known as the Data Pane. This Pane is where the Query view does the most of its work.

The graphic below depicts the previously established Web Data connection. The Overall score column has been selected, and its heading has been right-clicked to see the available menu selections. Many of these right-click menu choices are identical to buttons in the ribbon tabs.

Power Query Power BI - Data Pane in the Home Tab
Image Source

The query applies the step to the data when you select a right-click menu item (or a ribbon button). It also saves steps as a query parameter. The steps are recorded in the Query Settings pane in the order stated in the next section.

5) Power Query Power BI: The Query Settings Pane on the Right

The Right Pane, also known as the Query Settings Pane, displays all steps related to a query. For example, the Applied Steps area of the Query Settings pane in the accompanying image shows the fact that we just modified the type of the Overall score column.

Power Query Power BI - Query Settings
Image Source

Additional shaping steps are documented in the Applied Steps section when they are applied to the query.

It is critical to understand that the fundamental data is not altered. Power Query Editor, on the other hand, modifies and forms its perspective of the data. It also shapes and alters the view of any interaction with the underlying data that occurs as a result of the shaped and updated view of that data provided by Power Query Editor.

You can rename, delete, or reorder steps in the Query Settings window as you see fit. To do so, right-click the step in the Applied Steps section and select the appropriate option from the menu that appears. All query steps are executed in the order in which they appear in the Applied Steps window.

Power Query Power BI - Query Settings
Image Source

6) Power Query Power BI: Advanced Editor

The Advanced Editor displays the code that Power Query Editor generates at each step. You can also write your own shaping code. To access the advanced editor, choose View from the ribbon, then Advanced Editor. A popup appears, displaying the current inquiry code.

Power Query Power BI - Advanced Editor
Image Source

In the Advanced Editor window, you can directly alter the code. Select the Done or Cancel button to close the window.

7) Power Query Power BI: Saving your Work

When your query is where you want it, choose Close & Apply from the File menu in Power Query Editor. This action saves your changes and exits the editor.

As progress is achieved, Power BI Desktop displays a status dialogue.

Power Query Power BI - Saving Work
Image Source

When you’re finished, Power BI Desktop can save your work as a .pbix file. To save your work, go to File > Save (or File > Save As), as seen in the image below.

Power Query Power BI - Saving Files
Image Source

Benefits of Power Query Power BI

Here is a list of benefits that will keep you hooked up with!

  • Appropriate Connector: Power Query includes a plethora of data interfaces. Data sources such as TXT, CSV, and Excel files are supported, as well databases such as Microsoft SQL Server and major SaaS services such as Microsoft Dynamics 365 and Salesforce.
  • Early Filtering: It’s generally a good idea to filter your data as early as possible in the query process. It’s also a good idea to remove any data that isn’t related to your case. This allows you to concentrate on the task at hand by only displaying data that is important in the data preview section.
  • Modular Strategy: It is certainly possible to write a single query that incorporates all of the necessary transformations and calculations. However, if the question comprises a big number of steps, it may be a good idea to separate the query into many inquiries, each of which references the previous one.
  • Future-proofing queries: Power Query includes various capabilities that allow your query to be resilient to changes and to refresh even when some components of your data source change.
  • Create Reusable functions: If you need to apply the same set of changes to different queries or values, developing a Power Query custom function that can be reused as many times as you need could be advantageous.

Conclusion

Power Query in Power BI is a useful tool for importing data from many sources. While Power Query is confined to Excel sheets and CSV file formats, why not import data from databases such as MySQL and PostgreSQL, SaaS services such as Mailchimp and Zendesk, and CRMs such as Salesforce and HubSpot into Power BI.

Hevo Data is a No-Code that supports data ingestion from multiple sources be it your frequently used databases or SaaS applications like MySQL, PostgreSQL, Salesforce, Mailchimp, Asana, Trello, Zendesk, and other 100+ Data Sources. Hevo migrates your data to a secure central repository like a Data Warehouse in minutes with just a few simple clicks.

Using Hevo is simple, and you can set up a Data Pipeline in minutes without worrying about any errors or maintenance aspects. Hevo also supports advanced data transformation and workflow features to mold your data into any form before loading it to the target database.

Visit our Website to Explore Hevo

Hevo lets you migrate your data from your favorite applications to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt, within minutes to be analyzed in Power BI.

Why not try Hevo and the action for yourself? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also check out our pricing plans to choose the best-matched plan for your business needs. 

Share your knowledge after going through the Comprehensive Guide for Power Query Power BI in the comment section below! We would love to hear your thoughts.

No-code Data Pipeline for Power BI