AutoUpdateChartsinPQ

Automatically Update Your Excel Chart With New Data Using Power Query

When you add data to an Excel spreadsheet, with regular charts, you often need to trigger a refresh to make sure that your chart reflects the latest data. But, by using Power Query, you can automate that process without the need for macros. In this post, I’ll walk you through the process of getting your data into Power Query, and how to set it up so that your charts will update automatically.

Step 1: Importing Data into Excel with Power Query

The first step involves pulling data into Power Query. This can be from various sources like databases, web pages, or local files. In this example, I’m just going to use data that’s on another sheet, but it can also be from another workbook. Here is an excerpt of some sales data since the start of the year:

Table showing daily sales data in Excel.

To get this into Power Query, I just need to click on any of the cells in the table and then under the Data tab, under the Get & Transform Data section, select the From Table/Range option. Now my data is in Power Query.

But before loading the data back into Excel, I’m also going to group the totals by week. To do this, I’ll click on the Group By option in the Home tab in Power Query. I’ll create a column name called Weekly Total and sum the Sales Amount:

Grouping sales by week in Power Query.

Now I have sales broken down by week which I can import back into Excel.

Sales data in Power Query broken down by weeks.

At this stage, I’ll click on Close & Load. Now I have another table of the data in Excel, this time, linked to Power Query and broken out by week:

Sales data from Power Query broken down by week.

Step 2: Creating a Chart from Imported Data

Next, let’s go ahead and create a chart to show these daily sales totals. For this example, I’ll use a simple column chart showing the weekly sales. To do this, click on your data set anywhere and on the Insert tab, select the option for a Column Chart. After applying some formatting, this is what my chart looks like:

Chart showing weekly sales data in Excel.

Step 3: Setting Up Automatic Refresh

With my chart and table now setup, I can go ahead and set the automatic refresh. When a query is created in Power Query, you will see it under the Queries & Connections pane. To show this pane, go under the Data tab and click on Queries & Connections. Then, right-click on the query and select Properties. You’ll now see the following options:

Query properties in Excel.

As you can see, there is an option to specify how often you want to refresh the data. You can have it refresh when you click on the Refresh All button but you can also set it to refresh when the file first opens. And you can even specify it to refresh every few minutes. I can even set it to refresh every minute:

Query properties in Excel set to refresh every minute.

Depending on how often your data may change, you may want to adjust this accordingly. But one thing to keep in mind is that whatever changes you made in Power Query, refreshing the query will trigger all those steps, which can make it time consuming if there are many steps for the query to go through.

But by setting up a rule to refresh every x number of minutes, you can have control over how often your data updates. And since it’s linked to a chart, your chart will also automatically update.


If you like this post on How to Automatically Update Your Excel Chart With New Data Using Power Query please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

RunningTotalsinPQ

How to Calculate Running Totals in Power Query

Do you have year-to-date values and need to calculate running totals? What about if you have criteria you want to track running totals for? In this post, I’ll show you how to accomplish both and how you can create a custom function in Power Query that you can re-use in other queries. Here is a simple table I am going to use for this example:

Table in Excel showing sales by day, rep, and location.

In this table we have date, sales, salesperson, and location. Initially, I am just going to calculate the running total, regardless of the salesperson. I just want to know how much everyone has generated up to a certain point in time. The one step that I need to do is sort the dates in order, so they are going from smallest to largest. You can do this in either Excel or within Power Query.

Creating a running total for everything in Power Query

Step 1: Load your data into Power Query

To load your existing table into Power Query, click on your table and select the Data tab and click on From Table/Range.

Selecting the From Table/Range button in the Data tab.

Step 2: Sort your data

If your data is not already sorted in Power Query, then select the Date column and click the button to sot in ascending order.

Sorting data in Power Query.

Step 3: Create an Index column

On the Add Column tab, select the option to insert an Index Column, and select to the option to start From 1:

Creating an index column in Power Query.

This now creates a simple column that starts from 1.

A Power Query table with an index column.

Step 4: Create a column for the Running Total calculation

Select the Custom Column button from the Add Column tab, which will let you create a column based on a formula.

Selecting the Custom Column button from the Add Column tab.

There are two functions will be used in the formula. The first is the List.Sum function, which as the name describes, will sum up the values in a list. Within that will be the List.FirstN function which will grab all the index values up until that point. The custom column formula to enter, is as follows:

=List.Sum(List.FirstN(#"Added Index"[Sales],[Index]))

This is how it appears within the Power Query screen:

Creating a formula for a custom column in Power Query.

This now creates a Running Total column which calculates the year-to-date sales values:

A Power Query table after adding a Running Total column.

Creating a running total for when criteria is met in Power Query

This time around, I’m going to show you how to create a running total which resets for each salesperson. By doing this, you can track their individual year-to-date totals. If you’re starting from scratch, you will need to repeat the steps from the previous section in order to arrive at your Running Total formula.

Step 1: Convert the Running Total formula into custom function

Once you have created the Running Total formula, you can convert it into a custom function. If you select the query and click on the Advanced Editor button on the home tab, you will see the code for all the steps which were previously created:

Power Query advanced editor showing multiple steps.

To reuse this code and use it as a function, I need to add a place to enter a variable at the beginning.

let runningtotal = (x) =>

In the above example, x is the input. It will refer to the table which the custom function will execute on. And at the end of the custom function, there should be another ‘in’ keyword to circle back to the function, such as follows:

in runningtotal

Here is how my custom function looks like after removing the Changed Type step and setting the source equal to the variable:

The advanced editor showing a formula to calculating a running total.

If you remove any steps, however, just remember to update the formulas. In the “Added Index” step, I updated the step I’m referencing to Source. Otherwise, it would reference a step that doesn’t exist and would result in an error.

After saving, you should notice an fx function next to your query, which you may want to now rename to your desired function name. This is what you will reference it to when invoking the function. In this example, I’m going to call it RunningTotal to keep everything consistent.

Now the function is ready to be used.

Step 2: Group the data

On the Home tab, select the Group By button. This will allow you to break your data into multiple tables.

Selecting the Group By button in Power Query.

In the Group By section, select the option to group by Salesperson and use All Rows as the operation, which ensures no data is lost. I’ll name the new column Data.

Grouping data by salesperson in Power Query.

This now creates a column that contains tables based on the salesperson:

Power Query data which has been grouped by salesperson.

Step 3: Invoke the custom function

On the Add Column tab, press the button to Invoke Custom Function

Invoking a custom function in Power Query.

Then, on the details, enter the column name, the function to invoke, and the data which contains the variable. Since the new ‘Data’ column contains the table, that will be the variable in this example. But if you named the column differently, you would use that name.

The invoke custom function dialog box.

This will now create an additional column, which also contains a table.

A Power Query table after invoking a custom function to create a new column.

If you click on any of the cells in the newly created field, you’ll see that the preview contains the running total field:

A preview in Power Query of a table nested within a field.

Step 4: Expanding the table

The last step involves expanding the tables in the newly created field. By pressing on the two arrows going in opposite directions, you will be able to expand all of the columns.

Expanding a table in Power Query.

You can omit including the Index column as that is not necessary, and the Salesperson field is already there. The only other column you may want to remove in addition to this is the Data column, but that has to be removed with an additional step. Here is how my final table looks:

A Power Query table after adding a field for the running total.

You can see that as the name changes to a different salesperson, the running total doesn’t continue adding; it starts from the first date for that salesperson.


If you liked this post on How to Calculate Running Totals in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

sumgroupPQ

How to Sum and Group Values in Power Query

Have data in Power Query that you want to sum up and group by category? In this post, I’ll show you how you can sum up values in Power Query to help you analyze your information. In this example, I’m going to use daily sales values by different sales reps.

Sales data in Power Query.

Getting a simple sum

If you just want to calculate a sum in Power Query, select the column you want to sum, and then on the Transform tab, select Statistics, and select Sum.

Summing up a column.

This will create a new step, where it will calculate the sum.

The calculated sum step in Power Query.

You could reference this step in other calculations. However, if you want to tally up sales by categories, there’s a better way to do this.

Summing values using Group By

One way to both group and summarize your totals is by using the Group By method. To do this, select the column you want to group by. In this case, it’s going to be by the sales rep, since I want to see the total sales by rep. Then, on the Transform tab, I’ll click on the Group By button:

Selecting the group by method in Power Query.

Then, I’ll enter a column name of Total Sales and for the operation, select Sum and select Sales Amount ($) as the column I want to sum.

Setting the group by parameters in Power Query.

After pressing OK, I have a breakdown of sales by the different reps.

Grouping sales by sales rep in Power Query.

There are, however, more splits that you could do. Suppose you wanted to group sales rep sales by the type of product that was sold and the region. To split into more categories, select the Advanced option in the Group By dialog box. From there, you’ll have the ability to specify more levels to break sales down by.

Using advanced rules for the group by method in Power Query.

I can click on Add grouping to add more layers, such as Product Category and Region.

Adding more groupings in Power Query.

Now I have summations based on those different splits.

A summary in Power Query which shows totals by the different categories.

One thing you may still want to do at this point is to organize the data so that it is in some sort of order. To apply a sort, select the column you want to sort by and on the Home tab, indicate whether you want it to be in ascending or descending order. In the below example, I have arranged the data by sales rep, then product category, and then by region.

Data that has been summarized, grouped, and sorted in Power Query.

If you liked this post on How to Sum and Group Values in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

ReplaceNullValueswithAveragesinPQ

Replace Missing Values in Power Query With an Average

A common problem you might come across with data is that it may sometimes contain missing values. You can remove the data entirely or you can replace the values with something else. If you remove it, you might throw away other useful data related to that record in the process. If you replace it, you don’t want to just replace missing values with a zero, as that can impact your calculations. The optimal choice may be to replace it with the average of the other values. In this post, I’ll show you how you can easily do that in Power Query.

Calculating an average in Power Query

Here is the data set I’m starting with in Power Query, where you’ll see numerous null values in the Price field:

A data table in Power Query which is missing values.

To calculate an average in Power Query, follow these steps:

Select the column you want to calculate the average for. On the Transform tab, select Statistics and select Average.

Select the average function in Power Query.

Power Query now creates a separate step for me which has calculated the average:

Power Query calculating the average of the Price field.

To easily reference this value later on, I’m going to rename this step as AveragePrice.

Replacing the values

Now that you have calculated the average, you can use it to replace the null values in your data set. To do this, you’ll again need to highlight the column which contains null values. Right-click and select Replace Values:

Replacing values in Power Query.

Enter ‘null’ as the value you want to replace and just enter ‘1’ as the value to replace it with. This will just be a temporary placeholder.

Replacing null values with a 1 in Power Query.

In the formula bar, replace the ‘1’ with the name of the step — AveragePrice:

Adjusting a formula in Power Query so that it references the average price.

You’ll get an error saying there is a circular reference:

A cyclic reference in Power Query.

To fix this, drag the new step you just created so that it comes after the AveragePrice calculation step.

Moving the replaced value step in below the AveragePrice step in Power Query.

This still results in an error, and that’s because the formula is now referencing the AveragePrice step. This needs to be adjusted so that it references the Source step — or the one which contains the data immediately before the average price calculation.

Adjusting the Power Query formula so that it references the Source step.

Now the field is correctly updated and the null values have been replaced with the average for the column:

Power Query table after the null values have been replaced with an average.

In this situation, we now have eliminated the null values while being able to keep the other fields and simply replaced the empty values with averages.


If you liked this post on how to Replace Missing Values in Power Query With an Average, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

CryptoUsingPowerQuery

Get Crypto Prices for Multiple Coins Into Excel Using Power Query

Do you want an easy way to pull in crypto prices for multiple coins? Using Power Query in Excel, you can accomplish this by connecting to a website such a coingecko.com and downloading the historical values. Below, I’ll show you how you can pull in historical prices for multiple coins at once, and how you can easily update the values in the future.

Here are the steps to download crypto prices and import them into Power Query from coingecko.com:

  1. Connect to the data source using Power Query.
  2. Download the files into a folder.
  3. Combine and Transform the files in Power Query.
  4. Modify the data to make it consistent.
  5. Update the data.

Step 1: Connect to the data source using Power Query

In this example, I’m using coingecko.com as my source but the process may be similar for other sources. As long as the data as in a table format, there may not be a big difference in the process, however, there could be subtle changes in how you get data from one site versus another.

First, navigate to the coingecko.com website to the cryptocurrency’s price history that you want to download. On the historical data tab, there is a link to download the data:

Historical price data for bitcoin.

Step 2: Download the files into a folder

Download this file and save it into a folder. Repeat the process for any other cryptocurrencies that you want to track historical price information for. In this example, I’ve downloaded the price history for Bitcoin, Ethereum, Shiba Inu, and Dogecoin, and saved it within a folder called ‘Crypto’ on my computer:

Crypto excel files in a folder.

Step 3: Combine and transform the files in Power Query

Now that all the files have been downloaded, I can use Power Query to consolidate them. With all the files in a folder, I can go and select to get data From Folder:

Selecting to get data from a folder.

Then navigate to the folder which contains your downloads:

Folder which contains downloaded files to consolidate in power query.

Since you are selecting a folder, you won’t see the individual Excel files that you have saved — this is fine. Only if you were selecting files would you see the actual files. Once you’ve selected the correct folder, click on the Open button.

Next, select the option to Combine &Transform the files. If you get an error saying that it is an unexpected format, you may need to click on Edit on the next screen. This is because in this example, the data is in a comma-separated value format. After clicking edit, make sure you select the option for a CSV document:

Selecting the option to open the files as a CSV document.

Then, at the next screen, you’ll see that the data has correctly been broken out into columns.

CSV data in Power Query broken out into separate columns.

This is how the data looks loaded in Power Query:

Combined crypto data loaded into Power Query.

Step 4: Modify the data to make it consistent

There are multiple changes that need to be made to the file. The first is to adjust the source.name field so that it reflects the coin and doesn’t include the full filename. To do this, click on the Transform tab and select the option to Extract Text Before Delimiter and use as the delimiter.

Extracting text before delimiter in power query.

Then, I’ll rename the first two headers to ‘symbol’ and ‘date’

Power Query table after renaming columns.

Next, to correctly parse out the date, I’m going to grab the first 10 characters in the field. To do this, select the date column, and on the Transform tab, select Extract and select First Characters:

Extracting the first characters in a Power Query field.

Specify 10 for the number of characters, and Power Query will remove the rest:

Power Query table after the date field has kept only the first 10 characters.

I’ll remove the market_cap and total_volume columns since they aren’t needed. The last step involves pivoting the data so that the crypto symbols are going across. To do that, select the symbol column, and on the Transform tab, click on the Pivot Column button:

Pivoting a column in Power Query.

Select ‘price’ as the values column. Then, the end result should show the crypto prices going across:

Power Query table with crypto prices going horizontally and dates going vertically.

Step 5: Update the data

Now that the data is all entered in Power Query format, the process of updating it at a later date is fairly straightforward. Simply download the Excel files again, save them into the same folder (overwriting the previous files), and then click on the Refresh Data button on the Data tab:

Refreshing data in Power Query.

Clicking the button will refresh all the queries and do all the transformations and adjustments that were made earlier. By setting this all up in Power Query, you can easily repeat the process. Just download the latest data, and then click Refresh All.


If you liked this post on Get Crypto Prices for Multiple Coins Into Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

CryptoCoinDominance

How to Calculate Crypto Coin Dominance Using Power Query in Excel

Crypto coin dominance is a vital metric in the cryptocurrency market, providing insights into the relative market strength of a particular cryptocurrency, compared to the overall market. It reflects the proportion of a specific cryptocurrency’s market capitalization in relation to the total market cap of all cryptocurrencies. This metric is particularly significant for investors as it indicates the level of risk, market sentiment, and the dominance of major players like Bitcoin and Ethereum.

Formula for calculating crypto coin dominance

To calculate crypto coin dominance, you the formula itself is fairly straightforward: divide the market capitalization of the cryptocurrency in question by the total market capitalization of all cryptocurrencies. Market capitalization, in this context, is calculated by multiplying the current price of the crypto coin by its total circulating supply.

For instance, if Bitcoin has a market cap of $700 billion and the total market cap of all cryptocurrencies is $2 trillion, Bitcoin’s dominance would be 35%.

Why is this useful for investors?

Crypto coin dominance helps investors understand the weight of a particular cryptocurrency in the market, aiding in diversification and risk assessment strategies. A high dominance might suggest a more stable investment but with potentially lower growth prospects, while a lower dominance could indicate a more volatile but possibly high-growth opportunity. Additionally, shifts in dominance can signal broader market trends, helping investors to anticipate and react to market movements.

Calculating crypto coin dominance in Excel is a practical way for investors to actively monitor these shifts and make informed decisions. By regularly updating the market cap data for various cryptocurrencies, investors can use Excel to quickly calculate and track changes in dominance, enabling them to identify trends and adjust their portfolios accordingly.

Pulling in crypto market caps into Excel

To calculate coin dominance in Excel, you can use Power Query. Through Power Query, you can pull in the data, and do calculations to determine the dominance percentage.

You can find a list of the top cryptos by market cap from the following URL in Yahoo! Finance: https://finance.yahoo.com/u/yahoo-finance/watchlists/crypto-top-market-cap/

To get this data into Excel, take the following steps:

Go to the Data tab and select ‘From Web’

Selecting the From Web option in the Data section of Excel.

Paste the link the following prompt and click OK.

Using the From Web option in Power Query to import data.

Select the table in Power Query which contains data on the crypto market caps. Then Load the data.

Selecting a table to import from Power Query.

The data is now in Excel but there are no calculations happening just yet. To get this to work, we need the total crypto market cap. The table in Yahoo! Finance didn’t have this information readily available to pull into Power Query. Instead, I’ll leave a place in Excel where the data can easily be entered.

Currently, the crypto market cap is $1.8 trillion. Since the majority of crypto market caps are in billions, I’ll put this in the form of billions, as 1,810.

Exporting data into Power Query from a range in Excel.

Next, I’ll load this into Power Query. With one of the cells selected, click on the Data tab again, this time, select From Table/Range

Exporting data into Power Query using the From Table/Range option.

In Power Query, I’m going to rename this table MarketCap and the other one as YahooFinance.

Calculating coin dominance in Power Query

Before I start my calculations, I need to first convert the market caps from the YahooFinance in terms of billions, and remove the ‘B’ that comes after them. Currently, those values are reading as text, and they need to be numbers. In the YahooFinance table, highlight the column which contains the market cap. Then, on the Transform tab, click the Extract option and select Text Before Delimiter

Selecting a column in Power Query to parse out numbers from.

Just enter the letter ‘B’ for the delimiter and press OK on the next screen.

Parsing out the text before a delimiter in Power Query.

Then, it’s necessary to convert the text into numbers. For that, select the ABC indicator on the header for the market cap, and select Decimal Number for the type.

Converting a column into a decimal number format.

After doing so, the data in the column aligns to the right, indicating the field is a number.

The last part involves creating a new column to calculate the market dominance percentage. For this, go onto the Add Column tab in Power Query. Then, select the option for a Custom Column.

Adding a custom column in Power Query.

I’m going to name the column ‘Dominance’ and this is where I’ll need to enter my formula. The numerator will be market cap column, which in this case is Column9. I will divide this by the value in the MarketCap table. To reference that value, I need to use the following syntax: MarketCap[MarketCap](0}.

The table is called MarketCap, as is the field name, which is in parenthesis. Since I want the first value in the list, I use {0} since it is a 0-based index. That returns the total market cap I entered of 1,810. To put this all together, this is what the formula looks like in my Custom Column calculation:

Custom column calculation in Power Query which calculations coin dominance percentage.

After clicking OK, now my dominance column appears. Now I can load the data into Excel. All that’s left is to convert the column into a percentage.

Excel table showing crypto coin dominance.

Power Query will create another sheet for the MarketCap, but you can delete that as it isn’t necessary.

Updating the file

Moving forward, to update the calculations, all you need to do is update the MarketCap value. This is the only value that won’t pull in automatically from the Yahoo! Finance link. Then, click on the Refresh button on the Data tab and Power Query will pull in the updated market caps for the top cryptocurrencies, and then do the dominance percentage calculation.

Refreshing the data in Power Query.

Ideally, the total market cap could also be pulled in. However, given the vast number of cryptocurrencies, the list in Yahoo! Finance isn’t comprehensive enough; nor is there a table to pull just from market cap. If you do come across a better source for pulling in crypto market caps, let me know!


If you liked this post on How to Calculate Crypto Coin Dominance Using Power Query in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

PowerQuery101

Power Query for Beginners: A Comprehensive Guide

Excel is a great program for data analysis. And one of the key tools that analysts can use within it is Power Query. While it can seem intimidating for novice users, this guide will walk you through how to use it and how it can help you analyze data.

Getting started with Power Query

Power Query is an Excel tool that enables users to connect, combine, and refine data sources easily. It’s especially useful for automating the data cleaning and preparation process. With Power Query, repetitive tasks like importing data, filtering, sorting, and other transformations become streamlined, saving valuable time.

Power Query was first introduced as an add-in for Excel in 2010 and 2013. However, it became a built-in feature starting from Excel 2016. In these later versions, it’s integrated into the Data tab in the Excel ribbon, offering a more seamless experience compared to the add-in version for earlier Excel releases. Users of Excel 2010 and 2013 can still access Power Query, but they need to download and install the add-in separately. In Microsoft 365 (formerly known as Office 365), Power Query is fully integrated into Excel.

How do I launch Power Query?

To launch Power Query and get started with it, you’ll first want a table or data set in mind that you want to work on. You could link to an external website, workbook, or just have a table or data set within your worksheet that you want to work on. Power Query, after all, is a tool for data analysis — you need data to start with. Here are some of the more common ways to launch Power Query:

1. Connecting to an External Workbook

  • Navigate to the Data Tab: Go to the Data tab in the Excel ribbon.
  • Get Data: Click on Get Data in the Get & Transform Data section.
  • Choose the Source: Select From File and then choose From Workbook.
  • Select the Workbook: Browse and select the external Excel workbook you want to connect to.
  • Load or Transform: Once you select the file, you can choose to either load the data directly into Excel or open the Power Query Editor to transform the data before loading.

2. Connecting to a Webpage

  • Navigate to the Data Tab: Go to the Data tab.
  • Get Data: Click on “Get Data” in the Get & Transform Data section.
  • Select Web as Source: Choose From Other Sources and then select From Web.
  • Enter the URL: Enter the URL of the webpage you want to import data from.
  • Load or Transform: After connecting to the webpage, you can choose to load the data directly or use the Power Query Editor for transformations.

3. Using a Range or Table Within the Existing Sheet

  • Create or Select a Range or Table: You don’t need to have your data formatted in a table for Power Query to load it, however, Excel will convert it to a table once you launch Power Query.
  • Navigate to the Data Tab: Go to the Data tab.
  • Get Data: Click on Get Data in the Get & Transform Data section.
  • Choose From Table/Range: Select From Table/Range.
  • Power Query Editor: This will open the Power Query Editor with the selected table data, ready for transformation.
The Get & Transform Data section of the Excel ribbon.

Each of these methods serves a different purpose. Connecting to an external workbook is useful for consolidating or analyzing data spread across multiple Excel files. Connecting to a webpage allows for the import and analysis of data published online, such as tables on web pages. Using a table within the existing sheet is handy for quickly transforming or analyzing data already present in your workbook. In all cases, Power Query provides a robust set of tools for manipulating the data before loading it back into Excel for further use.

What can Power Query do?

Power Query can help adjust your data before loading it into Excel. Here are some of the key things you can do with it:

  • Transform data types
  • Handle missing data
  • Remove duplicates
  • Replace values
  • Filter data
  • Sort data
  • Concatenate columns
  • Split columns
  • Add conditional columns
  • Group and aggregate data

Want to follow along with these examples? Download the sample data set I will use here.

Transform data types

One of the first things Power Query does is it attempts to detect your data types. And when it does, it automatically adjusts them, so that numbers are formatted as numbers, and dates as dates. The Changed Type step appears automatically:

The changed type step in Power Query.

If, however, it hasn’t correctly applied a data type, you can make changes to it. You can specify many different data types, including:

  • Text
  • Whole Number
  • Decimal Number
  • Date
  • Time
  • Date/Time
  • Boolean

To change a data type, click on the header which shows the data type that it is, and then you’ll see a list of different options to choose from:

Drop-down options for selecting various data types in Power Query.

Handle missing data

Power Query in Excel is equipped with a variety of tools to effectively manage missing or null data, a common issue in data analysis. Ensuring accurate handling of missing data is crucial for the integrity of your analysis. Here are the ways Power Query can assist in managing missing data:

  • Highlight Null Values: Power Query visually represents null or missing values, making it straightforward to identify gaps in your dataset.
  • Remove or Keep Rows with Missing Values: You have the option to filter out rows that contain missing values in one or more columns, useful for analyzing complete records. Alternatively, you might want to focus specifically on rows with missing data.
  • Replace Nulls with Specific Values: Power Query allows for the replacement of missing values with a specified value, such as zero, a specific text string, or an average value. This is beneficial where a missing value has a logical default or substitute.
  • Fill Down or Up: Filling missing values with the value from the row above or below is possible, which is helpful in datasets where the missing value logically mirrors its neighbor.
  • Conditional Replacements: Implement logic to replace or handle missing values based on certain conditions, catering to different scenarios for different categories or columns.
  • Aggregate Functions: When performing functions like sums or averages, Power Query automatically accounts for null values, ensuring they don’t skew the results.
  • Merging with Missing Data: While merging tables, Power Query can be set up to handle missing values in key columns in various ways, such as including or excluding unmatched rows.
  • Data Type Impact: Changing data types might create missing values (like when a text can’t be converted to a number). Power Query helps in identifying and handling such cases effectively.

Removing duplicates

Removing duplicate values is an important part of the cleanup process when it comes to data analysis. If, for example, you’re working with a list of transactions or customer records, removing duplicates ensures that each record or transaction is unique and correctly represented in your analysis. And in Power Query, the process to remove duplicates is a fairly straightforward one. It’s as simple as selecting the column and selecting the option to Remove Duplicates.

Removing duplicate values in Power Query.

You can also apply this for multiple columns at once. To select more than one column, just hold down the CTRL key while clicking on other column headers.

Replacing values

Replacing values in Power Query is a useful feature for cleaning and standardizing your data. It allows you to substitute specific values in your dataset with new ones, which is particularly helpful in correcting errors, standardizing terminology, or handling missing data. Think of it like using the Find and Replace feature in Excel. Here’s how it works:

  • Select the column you want to replace values on. Similar to with removing duplicates, you can select multiple columns.
  • Select the option to Replace Values.
  • In the next dialog box, select the value you want to find and what to replace it with.
Replacing values in Power Query.

Filter data

Filtering data in Power Query is a fundamental aspect of data preparation and analysis, allowing you to narrow down your dataset to only the information relevant for your specific needs. The filtering process is similar to how you would do it in Excel. Here’s how it works:

  • Select the column you want to filter.
  • Click the drop-down arrow, and you can select how you want to apply your filter:
Filtering data in Power Query.

These are the same type of filters you can apply as in Excel, including criteria such as “contains”, “does not contain”, “starts with”, etc. For numeric columns, you can choose from number filters like “equals”, “greater than”, “less than”, and you can specify ranges. For date columns, you can filter by specific dates, before/after a certain date, or choose from a range of date filters.

After setting your filter criteria, Power Query will display only the rows that meet the criteria. You can apply multiple filters across different columns.

Filtering in Power Query is a versatile tool, allowing for basic operations like removing irrelevant rows, as well as more complex data segmentation, which is essential for detailed and accurate data analysis.

Sorting data

Sorting data in Power Query allows you to organize your dataset in a meaningful order, making it easier to analyze and understand. And by doing it in Power Query, when the data is loaded into Excel, your sorting rules have already been applied. Just like with filtering, the process for sorting data in Power Query is also comparable to how you would do it within your spreadsheet:

  • Choose the column to sort by.
  • Select the sort order. For a simple, one-level sort, use the sort ascending (A to Z) or sort descending (Z to A) buttons in the Home tab of the Power Query ribbon. To sort ascending, click the small arrow in the column header and choose “Sort Ascending”. This will organize the data in that column from lowest to highest (e.g., A to Z, 0 to 9, earliest to latest date). To sort descending, click the arrow and choose “Sort Descending”. This will organize the data from highest to lowest (e.g., Z to A, 9 to 0, latest to earliest date).
Applying a sort to a Power Query column.

If you want to sort by multiple columns (for example, first by state, then by city), sort the most significant column first, and then sort by the next column. When you apply multiple sorting rules, you will see a number next to the sorting icon to show you its priority. In the screenshot below, the State field has a 1 next to the up arrow, indicating that the data is first sorted in ascending order by state. Then, it sorts the City field in descending order.

Multiple sorting rules applied in Power Query.

Concatenate columns

Concatenating fields (or columns) in Power Query involves combining the contents of two or more columns into a single column. This process is useful for creating unique identifiers, combining textual information, or formatting data in a more useful way. In this example, we’ll combine the State and City fields, by taking the following steps:

  • Select the two or more columns you want to concatenate or merge together. The order of your selection is important. If you want the City field first, then that is the one you need to select first.
  • Right-click and select the option to Merge Columns
Selecting the option to merge columns in Power Query.
  • Specify if you want them to be separated in any way. In this example, we’ll use a comma so that it is in City,State format.
Merging columns in Power Query.

This results in a field that has grouped the two columns. Those two columns have now been replaced with the new merged column.

Merged column showing city and state.

Split columns

Splitting columns in Power Query is a handy feature that allows you to divide the contents of one column into multiple columns. This can be particularly useful when dealing with data that’s concatenated into a single column but needs to be separated for analysis or reporting. Here’s how we can undo the previous step, to break out the new City,State column back into separate columns:

  • Start with selecting the column to split.
  • Right-click on the option to Split column. Then select how you want it to be split.
Selecting the option to split a column in Power Query.

These are the ways you can split them:

  • By Delimiter: Split the column based on a specific character or symbol, such as a comma, space, or custom character. This is the most common method used when data in a column is separated by a consistent symbol.
  • By Number of Characters: Split the column into new columns each containing a specific number of characters.
  • By Text Length: Split the column at a specific character position.
  • Advanced Options: Allow for more complex splits, such as splitting at the first or last occurrence of the delimiter.

In the City,State field, we can split by delimiter — the comma. You can specify how it should be split. But in our example, the default options will suffice:

Splitting a column by delimiter.

This will split the columns back into two:

A concatenated column in Power Query has been split back into two separate ones.

The only thing that may be necessary to do is to rename the columns. To do that, just double-click on the headers and type in a new name for the columns.

Add conditional columns

Adding conditional columns in Power Query is a powerful way to create new columns based on conditions derived from other data in your table. It’s akin to using the IF function in Excel but allows for more complex and multiple conditions. Here’s how you can add a conditional column in Power Query:

  • Select the Add Column tab on the ribbon.
  • Select the option to add a Conditional Column.
Adding a conditional column in Power Query.

Next, you can create your conditions, and the column name. With the data set in this example, we can set up a column called Discount Category based on the sales price. This can tell us the type of discount a customer is eligible for. The conditions could be as follows:

  • If Sales > 3000, then “High Discount”
  • If Sales is between 1500 and 3000, then “Medium Discount”
  • Else, “Low Discount”
Adding a conditional column in Power Query.

In the above example, the criteria is evaluated in order from top to bottom. This now creates a new column in the table:

Discount category created in Power Query.

Group and aggregate data

Grouping and aggregating data in Power Query is a crucial process in data analysis, allowing you to summarize and analyze large datasets efficiently. This feature is especially useful for finding averages, sums, counts, minimums, and maximums for different categories or groups in your data. In this example, let’s total the sales by city. Here’s how:

  • On the Home tab, click on the Group By button
The Group By option in Power Query.
  • Select City as the initial field. This is how the data will be grouped.
  • Then, enter a column name (City Sales), an operation (Sum), and specify the column to tabulate (Sales)
Grouping sales by city in Power Query.

This now gives you a summary by city:

Grouping sales by city in Power Query.

You can also do more complex grouping by more than one field. To do so, select the Advanced radio button when in the Group By dialog box. Then, select Add grouping. In this example, we can add the Product_ID field as another field to group by.

The group by dialog box in Power Query.

Now, when the grouping is completed, it breaks it down further:

Grouping sales by multiple fields in Power Query.

Close & Load once you’re done making changes

Once you’ve finished making your changes in Power Query, you can select the option to Close & Load to get it into Excel.

What “Close & Load” Does:

  • Finalizes Changes: When you click “Close & Load”, Power Query applies all the transformations you’ve made to the dataset within the Power Query Editor.
  • Loads Data into Excel: After applying the changes, the transformed data is loaded into an Excel worksheet or data model. This can be a new sheet or table, depending on your settings and the nature of your task.
  • Creates a Connection: Power Query creates a connection between the source data and the Excel workbook. This connection is maintained, which means that you can refresh the data in Excel to reflect any updates in the source data or further transformations applied in Power Query.
  • Saves Transformations: The sequence of steps or transformations you applied in Power Query is saved. This allows for the data to be updated or reloaded with the same transformations applied automatically.

Why it’s beneficial to make changes in Power Query before loading it into your spreadsheet

You may be wondering why you wouldn’t just make all these changes in your spreadsheet. Why is there a need to make the changes in Power Query? Here’s why:

  • Data Size Management: Power Query can handle and process large datasets more efficiently than Excel. By filtering, reducing, and transforming data in Power Query, you minimize the load and improve performance in Excel.
  • Non-Destructive Data Manipulation: Changes made in Power Query don’t alter the original data source. This means you can experiment with and modify your data without the risk of corrupting the original dataset.
  • Automating Repetitive Tasks: Any sequence of steps you apply in Power Query is repeatable. If you regularly receive data in the same format, you can use the same query to process this data, saving time and effort.
  • Complex Transformations: Power Query offers more advanced data manipulation capabilities than standard Excel functions, including pivoting/unpivoting, advanced merging and appending, complex filtering, and more.
  • Data Cleansing and Preparation: It’s often necessary to clean and format data before analysis. Power Query provides a robust set of tools for handling common data issues like missing values, duplicates, and inconsistent formats.
  • Reduces Workbook Size: By transforming data in Power Query and loading only what’s needed, you reduce the overall size of the Excel workbook, leading to better performance and easier handling.

If you like this post on Power Query for Beginners: A Comprehensive Guide, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

ReplaceMultipleValues

How to Replace Multiple Values in Power Query

Replacing values can be an important part of cleaning up your data and preparing it for data analysis. Below, I’ll outline the steps to take to replace a value in Power Query. I’ll also show you how you can create a formula in Power Query to make it easy to replace multiple values at once.

Replacing a single value in Power Query

In the following data set, I have a list of orders. There are dates, order numbers, and statuses. Some of the statuses may be a bit similar so to reduce the number of them, it can make sense to replace values.

A list of orders and statuses in Power Query.

I am going to replace to the ‘Awaiting Authorization’ status to ‘Pending’.

Here are the steps needed to take to replace a value in Power Query:

1. Load your data into Power Query.

2. Right-Click on the column where you want to replace values and select Replace Values

Selecting the option to Replace Values in Power Query.

3. Enter the value to find and what to replace it with, and then click OK.

Selecting which value to replace.

Now, Power Query will replace the value for you:

Order table in Power Query after replacing a value.

This isn’t an ideal solution, however, because doing it this way would require you to repeat these steps over and over again. Instead, there’s another way to do this.

Replacing multiple values in Power Query at once

If you want to replace multiple values in a single step in Power Query, you can accomplish that through a formula. The Table.ReplaceValue function allows you to specify the values you want to replace. For instance, to replace just a single value, this would be the formula:

= Table.ReplaceValue(#"Changed Type","Awaiting Authorization","Pending",Replacer.ReplaceText,{"Status"})

Where #”Changed Type” is the name of the preceding step. In this formula, any instance of ‘Awaiting Authorization’ is replaced with ‘Pending’.

If you want to replace multiple values, then you can use if statements to check for multiple conditions:

= Table.ReplaceValue(#"Changed Type",each [Status], each if [Status] = "Awaiting Authorization" then "Pending" else if [Status] = "Awaiting Shipment" then "Pending" else [Status], Replacer.ReplaceText,{"Status"})

The same function is used. However, by using the ‘each’ keyword, it will now cycle through the values in the [Status] field. It will do the original search for ‘Awaiting Authorization’ and replace it with ‘Pending’. There is also an else if statement which allows the formula to go even further and also replace ‘Awaiting Shipment’ with ‘Pending’. Finally, if there are no matches for either of those terms, then it will just leave the value that is already in the ‘Status’ field.

You can even add more else if statements to replace more values if necessary. By doing this, you can make the process even more efficient by swapping out even more values through a single step.


If you liked this post on How to Replace Multiple Values in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

TrendingStocks

Get Trending Stocks Into Excel Using Power Query

In the fast-paced world of investing, identifying trending stocks in Excel can provide a valuable edge for investors seeking profitable opportunities. Fortunately, with the power of Excel’s Power Query and the ability to connect to a website’s API, accessing real-time data and uncovering trending stocks has become more accessible than ever. In this article, I will go through the process of using Power Query to connect to a website’s API and importing in trending stock information.

Why should investors try to identify trending stocks?

As an investor, it is crucial to identify trending and popular stocks for several reasons:

  1. Profit Potential: Trending and popular stocks often have significant profit potential. When a stock is gaining popularity, it usually attracts more investors, leading to increased demand and potentially driving up the stock price. By identifying these stocks early, you can position yourself to benefit from the price appreciation and generate higher returns on your investment.
  2. Liquidity: Popular stocks tend to have higher liquidity, meaning there is a larger pool of buyers and sellers in the market. This liquidity allows you to enter and exit positions more easily, ensuring that you can buy or sell shares without significantly impacting the stock’s price. Investing in liquid stocks provides flexibility and reduces the risk of being unable to execute trades at desired prices.
  3. Market Validation: The popularity of a stock often reflects positive market sentiment and investor confidence. When a company is trending and gaining attention, it may indicate that the market believes in its growth prospects and overall performance. By identifying such stocks, you can align your investment choices with market sentiment and increase the likelihood of investing in companies with strong fundamentals and future growth potential.
  4. Information Availability: Popular stocks generally attract more media coverage, research reports, and analyst attention. This increased coverage provides you with a wealth of information and analysis to make more informed investment decisions. You can leverage these resources to understand the company’s financial health, competitive position, industry trends, and other relevant factors that can impact the stock’s performance.

How to get trending stocks in Excel

To get trending stock data into Excel, you should start with finding a good source that you can rely on for trending data. For this example, I’m going to use apewidsom.io, which provides free access to its API using the following url: https://apewisdom.io/api/. Here’s how I’m going to use that to pull in trending data:

Extract the data using Power Query. To get started, I’ll select the Data tab in Excel and click on the From Web option.

    Select the From Web option in Get & Transform Data to set up Power Query so that it connects to a certain web page.

    Next, there will be a field to enter the URL, this is where I will paste the link that the API references:

    https://apewisdom.io/api/v1.0/filter/all-stocks/page/4
    

    Entering a URL in the From Web field for creating a Power Query connection.

    After clicking OK, Power Query will launch. When the screen opens up, the following table appears. I click on List to open up another table.

    Power Query results stored in a list.

    After clicking that, there’s another list of records.

    A list of records in Power Query.

    Here, I’ll select the option to convert to table and leave the default settings and click OK. Then, there is another list of records. Clicking on the button with the arrows going in opposite directions will expand them:

    Expanding a list of records in Power Query.

    After expanding out those records, the table will now looks like a list of stocks and metrics relating to mentions, upvotes, and overall rank popularity:

    Power Query table showing a stock's populating by mentions and upvotes.

    Now that this has been setup, I will convert this into a Power Query function. To do that, I’ll click on the Advanced Editor button:

    Selecting the advanced editor option in Power Query.

    In the editor, I will add a line at the top to specify the name of the function. And at the bottom, I will add a line to circle back to it. Lastly, I’ll add a variable for the URL as well, and put that where the link used to be:

    Power Query code for a custom function.

    Next, with the custom function created, I’m going to go back into Excel and create a list of all the URLs I want to use this function on. In this situation, I’m going to adjust the page number at the end of the URL so that I have pages 1 through 5:

    A table of URLs.

    I’ll load this table, called URLtable, into Power Query using the From Table/Range button when selecting data. Next, I’ll select the Add Column tab and select Invoke Custom Function:

    Selecting the option to Invoke a Custom Function.

    Then, I reference the query as well as the URL variable that is to be used:

    Selecting which custom function to invoke.

    Then, there will be a field with the results, in table format. Again, this needs to be expanded out:

    A list of results in Power Query after invoking the custom function.

    That will leave a list of stocks starting from page 1 all the way through page 5. You can remove the URL field, which is no longer needed:

    A Power Query table showing the list of popular stocks.

    If you don’t want to follow through all those steps yourself, you can download the template I’ve created here.


    If you liked this post on Get Trending Stocks Into Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

    CustomPQFunction

    How to Create a Custom Function in Power Query

    Excel’s Power Query is a powerful data transformation and analysis tool that allows users to retrieve, clean, and shape data from various sources. While Power Query provides an extensive set of built-in functions, there may be scenarios where you need to perform custom operations on your data. This is where custom functions in Power Query come into play. In this article, I will go over how to create a custom function in Power Query that you can invoke and re-use.

    Steps to creating a custom function in Power Query

    Creating custom functions in Power Query involves using the M language, which is the scripting language underlying Power Query. It can be complicated to create but I’ll show you two ways you can create a function. The first method is directly through coding, the other is after converting a query into a function.

    In this example, I’m going to pull all the stocks that are contained from a list of exchange-traded funds (ETFs). I’ve created the following table for this purpose, called tblETF:

    A table of different exchange-traded funds listed.

    Creating a custom function from scratch

    If you’re creating a function in Power Query directly from code, here’s how to do that:

    1. Go to load the data into Power Query by selecting a cell in your table, then click on the Data tab and click From Table/Range.

    Launching Power Query from the From Table/Range button.

    2. That will open up Power Query. Once there, on the Home Tab, click on the Advanced Editor button:

    Selecting the Advanced Editor option in Power Query.

    3. Create a name for the function using the let variable. In this example, I’m going to call it getholdings and it will pull all the holdings from the etf field. The opening line of the code is as follows:

    let getholdings = (etf) =>

    4. Next, list the commands that the function should execute. I’m going to pull the data from the stockanalysis.com page relating to the etf. This requires using the Web.Contents function and modifying the URL so that it includes the etf symbol:

    let

    Source = Web.Page(Web.Contents("https://stockanalysis.com/etf/"&etf&"/holdings/")),

    Then, reference the data as follows:

    Data0 = Source{0}[Data],

    The full code looks like this:

    Custom macro in Power Query that gets the ETF holdings.

    5. Now that the function is created, go into the query for the list of ETFs. Create an additional column from the Add Column tab, and select the button to Invoke Custom Function.

    Selecting the Invoke Custom Function option in Power Query.

    6. Set a column name for the new column. Then, specify the function query to reference. And you’ll also need to specify where the ETF value is coming from, which involves selecting the column:

    Invoking a custom function in Power Query.

    7. Next, you’ll expand the table that has been created within the column. This is done by pressing on the icon that shows arrows going in opposite directions. Then, select all the available columns.

    Expanding a table in Power Query.

    You should end up with something that looks like this:

    Fully expanded Power Query table showing holdings by ETF.

    You can now click Close & Load and this data will load in your Excel spreadsheet. Now you can add to your ETF list and refresh the data, and the table of all the holdings will populate.

    List of ETFs and their related holdings in two separate excel tables.

    Converting a query into a function

    If you’re not comfortable coding with Power Query, you can first create the steps, and then convert the query to a function.

    First, it’s necessary to create the query. In the previous example, I loaded the URL from a dynamic web page. To do that, I’ll start with selecting the From Web button on the Get & Transform Data section:

    Using the From Web button on the Get & Transform Data section in Excel.

    Next, populate the entire link, without the ETF variable — this will be added later:

    Setting the URL that you want to pull data from into Power Query.

    Then, select the table that contains the data and click the Load To button and select connection only:

    Selecting the table in Power Query that contains the ETF holdings.

    Then, right-click on the query to edit it so that you’re back in Power Query. From there, click on the Advanced Editor and you should see this:

    Power Query advanced editor showing the code to extract data from a web page.

    This is similar to the code in the first approach. To convert this into a function, we need to add another let variable and specify the function name, and any variables that will be used in the function. For the first line, I’ll add the following

    let getholdings = (etf) =>

    and for the URL, I’ll put the etf variable into there:

    Source = Web.Page(Web.Contents("https://stockanalysis.com/etf/"&etf&"/holdings/")),

    For the last line, I’ll add another in variable:

    in getholdings

    Here’s the updated code, with the changes highlighted in yellow:

    Now I’ve converted my query into a function that can be invoked.


    If you liked this post on How to Create a Custom Function in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.