Power Query for Microsoft Excel is a program designed to take large sets of data and allow you to make changes easier. It was created to make complicated tasks simple.

Importing data and performing basic changes in Microsoft Power Query is a good way to create a script, and begin learning how they work. Let’s go step-by-step into generating a script, making changes, and seeing the results.

The “M” Language

Before we dive in, a little to know about the language that runs Power Query. Microsoft created the “M” language to build Power Query.

If you’re not a programmer, not to worry. You don’t need to know how to code using the “M” Language immediately, but it is helpful to be aware of its format as you begin to learn Power Query


What Is Microsoft Power Query for Excel? 5 Reasons to Start Using It




What Is Microsoft Power Query for Excel? 5 Reasons to Start Using It

Microsoft Power Query for Excel is a new tool that gives you more control over your spreadsheets than ever before.
Read More

.

Basic “M” Language Format

In its most basic form the “M” language is structured as a “let-in statement”, like this:

M Language Code for Microsoft Power Query

In a Power Query script, let is where you place the code you wish to run (input), and in is where you wish to run it (output). The code that is run inside of the let statement can be made up of variables, expressions, and values.

Variables can be named in two ways. The first is with words and no spaces. For example: “Variable” or “MyVariable”.

If you would like to use a space in your variable name, just start with a #. For example: #”My Variable” or #”New Variable”. Each of these variables has a space in the name but is still valid.

With this in mind let’s get started on our Power Query Script.

Importing Data Into Power Query

For this example, let’s take a table we create called Fruits.xlsx which displays various fruits and their prices. There are some simple columns for "Fruit", "City", and "Price".

The goal of this tutorial is to take the "Price" column and convert the data from a number to a currency format using Power Query.

Data Table to Upload to Microsoft Power Query

We have this table saved to the desktop. Let’s create a new Excel workbook called Fruit Data.xlsx to import our data.

In the toolbar select Data and click on Get Data. This will give you options to choose your data source, we want it From File > From Workbook.

Get Data Menu for Excel Power Query

This will open your file explorer. Go ahead and select the Fruits.xlsx workbook to import, and click OK.

Using the Power Query Editor

Once you click OK, the Power Query Navigator will pop up to choose data in the workbook. Select the Fruit table from the workbook, a preview of the data you select will appear on the right, to confirm your selection.

Navigator Menu for Power Query Import

Click Transform Data to open the Power Query Editor.

Viewing the Script

At first glance, there is a lot going on, but let’s focus on getting the script.

With the Power Query Editor open, in your toolbar under Home click on Advanced Editor to open the script. The Advanced Editor will open, and inside you will see the Power Query script in the M Language.

Power Query Editor M Language

The contents seem very wordy, so let’s break down the script before we make a change.

Script Contents

First off, notice that the script is the “let/in” format we went over with the M Language. Inside of “let” are the lines of code that make your changes, and the “in” code is where the changes will be output.

Let’s go line by line to get a better feel for the code. This will need a close look and it may be easier to understand looking at it a couple of times.

letSource = Excel.Workbook(File.Contents("C:Fruit.xlsx"), null, true),
Fruit_Sheet = Source{[Item="Fruit",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Fruit_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fruit", type text}, {"City", type text}, {"Price", type number}})
in
#"Changed Type"

Script Breakdown

The code begins with a let statement, which starts every block of code. The first thing Power Query will do is import the file into a variable called Source which is simply the Fruit.xlsx file we chose to import.

Note the comma at the end. Each statement will end with a comma, which just tells the code to read the next line.

From there, Power Query takes the sheet from Fruit.xlsx and sets it to a variable called Fruit_Sheet.

Line three is using a function Table.PromoteHeaders that takes the column headers we were using in Excel and making them headers in Power Query.

The next line starting with #"Changed Type", Power Query is changing the type of each column’s data. The "Fruit" column is changed to type text, "City" is type text, and "Price" is type number. Power Query is smart and tried to determine what type of data is stored in the table.

Lastly, the in statement outputs the code to our editor, by outputting it to the last variable it can recognize which is #"Changed Type".

It’s important to recognize one thing here: Each variable created on a line is used in some fashion on the following line. Think of it like a chain where every link is connected to the last.

Editing the Query

Now that the script is in view and we have reviewed the code let’s make our edit. Remember, the goal was to take all the data in the "Price" column and change it to currency.

Fortunately, we already have an idea for how to accomplish this. Recall this line of code:

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fruit", type text}, {"City", type text}, {"Price", type number}})

We have the code which changed the data types for the entire table! This is where we can make our adjustment, and get our result.

Each column is assigned a type one-by-one, so let’s make the change directly in the code. Currently, the "Price" column is assigned type number. We want it to be currency, so let’s change this code to Currency.Type. 

Here’s what it looks like:

Revised Advanced Editor Script for Power Query

Click Done to be taken back to the main screen. The type symbol is now a dollar sign, which means the type has been converted to currency.

Updated Power Query Data Table

Better Data Analysis in Microsoft Excel

This was just scratching the surface of Power Query scripts using the M Language. With more power to work with Excel, it’s easier than ever to convert data to pivot tables to use for data analysis


How to Use an Excel Pivot Table for Data Analysis




How to Use an Excel Pivot Table for Data Analysis

The pivot table is one of the single most powerful tools in the Excel 2013 repertoire. It is frequently used for large data analysis. Follow our step-by-step demonstration to learn all about it.
Read More

 or make a chart in Excel.


How to Make a Chart in Excel




How to Make a Chart in Excel

Never created a chart in Excel? Here’s how to make a chart in Excel and customize it, using the most common chart types.
Read More

Source: A Step-by-Step Guide to Your First Microsoft Power Query Script

By Anthony Grant

Techylawyer and its authors do not claim to have written this article, we acknowledge the works of the original author

Leave a Reply