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.


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, which provides free access to its API using the following url: 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:

    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.


    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 page relating to the etf. This requires using the Web.Contents function and modifying the URL so that it includes the etf symbol:


    Source = Web.Page(Web.Contents(""&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(""&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.


    How to Do a Fuzzy Lookup in Power Query

    Power Query is a powerful data transformation tool in Excel that allows you to effortlessly connect to various data sources, cleanse and manipulate data, and unlock advanced functionalities such as fuzzy matching. By leveraging the inherent “Fuzzy Lookup” feature within Power Query, you can seamlessly compare and match similar values across columns or tables using intelligent fuzzy logic algorithms. This article will highlight how you can use this functionality to help consolidate data, improve accuracy, and correct mistakes.

    What is the purpose of a fuzzy lookup in Power Query?

    A fuzzy lookup in Power Query refers to a powerful feature that allows you to match similar or related records within your dataset, even if they contain variations or discrepancies. Unlike an exact match that demands identical values, a fuzzy lookup takes into account the degree of similarity between values using intelligent fuzzy logic algorithms.

    Imagine you have a dataset containing customer names, and you want to compare it with another dataset to identify potential matches. However, due to typos, misspellings, or differences in formatting, the exact matches may not yield the desired results. This is where a fuzzy lookup comes to the rescue.

    By using a fuzzy lookup, you can overcome these obstacles. Power Query evaluates the similarity between values based on factors such as spelling variations, phonetic similarities, transpositions, and even differences in word order. This flexible approach allows you to find connections between records that might have otherwise been missed and unmatched.

    There are many benefits of fuzzy lookups. They enhance data accuracy and integrity by enabling you to identify related records that might have been entered inconsistently. They can consolidate information from different sources, harmonize data formats, and facilitate a comprehensive analysis of your datasets.

    Fuzzy lookups are particularly valuable when dealing with large datasets, data integration, data cleansing, or any scenario where data inconsistencies are prevalent. They provide a robust mechanism to uncover hidden associations that might have otherwise resulted in incomplete data. Leveraging the power of fuzzy lookups in Power Query can significantly improve the quality of your data analysis.

    What is the difference between an exact match and a fuzzy match?

    An exact match refers to a comparison between values that must be identical in every aspect, including spelling, punctuation, and formatting. It requires an exact one-to-one correspondence between the compared values.

    A fuzzy lookup, however, takes a more flexible approach by considering variations, similarities, and patterns within the values being compared. It utilizes fuzzy logic algorithms to calculate the degree of similarity between the values, allowing for differences in spelling, formatting, and other factors. Here’s an example:

    Dataset 1: “Robert Johnson” Dataset 2: “Robert Johhnson”

    In this example, an exact match would fail to identify a match due to the slight difference in spelling (“Johhnson” instead of “Johnson”). However, a fuzzy lookup would recognize the similarity between the values based on the fuzzy logic algorithms, identifying them as a potential match.

    While an exact match demands complete identity, a fuzzy lookup offers a more lenient approach by accommodating variations, spelling differences, abbreviations, and even phonetic similarities. It enables the discovery of relationships and connections that might otherwise be missed, allowing for more comprehensive data analysis and data integration.

    The limitations of a fuzzy lookup

    While fuzzy lookups in Power Query offer a powerful mechanism for matching similar records, it is important to be aware of their limitations. Understanding these limitations can help you effectively address challenges and make informed decisions when utilizing fuzzy lookups in Power Query.

    1. Performance Impact: Performing fuzzy lookups on large datasets can have an impact on performance. Fuzzy matching involves complex algorithms that analyze the similarity between values, which requires additional computational resources. When working with extensive datasets, it is advisable to consider the potential performance implications and evaluate whether optimization techniques, such as limiting the scope of matching or using more specific matching criteria, are necessary.
    2. Configuring Fuzzy Matching Parameters: The success of a fuzzy lookup heavily relies on properly configuring the fuzzy matching parameters. Selecting the appropriate similarity threshold and adjusting other options, such as case sensitivity or accents, is crucial. However, finding the right balance can be challenging, as overly strict or lenient parameters may result in missed matches or false positives. It often requires experimentation and fine-tuning to achieve the desired level of matching accuracy.
    3. Data Quality and Variations: Fuzzy lookups are highly dependent on the quality and consistency of the data being matched. Inaccurate or inconsistent data, such as misspellings, abbreviations, or incomplete information, can impact the effectiveness of fuzzy matching. While fuzzy lookups can handle some degree of variation, extreme discrepancies or inconsistent patterns in the data may hinder accurate matching.
    4. Ambiguity and Multiple Matches: In certain cases, fuzzy lookups may encounter situations where multiple records match a single value, leading to ambiguity. This can occur when there are similar records or when the matching criteria are not precise enough. Dealing with such scenarios requires additional consideration and possibly manual intervention to determine the correct matches.
    5. Sensitivity to Dataset Size and Complexity: The effectiveness of fuzzy lookups can vary depending on the size and complexity of the dataset. Extremely large datasets or datasets with high variability in the values being matched can pose challenges. It is important to assess the scale and complexity of the data and consider alternative approaches, such as data preprocessing or dividing the task into smaller subsets, to manage the impact on performance and improve matching accuracy.

    While fuzzy lookups provide valuable capabilities for identifying similar records, it is essential to be mindful of these limitations. There can be a risk of relying too heavily on fuzzy matches which results in erroneous results. By understanding and addressing these limitations appropriately, you can maximize the benefits of fuzzy lookups in Power Query and make informed decisions when incorporating them into your data analysis workflow.

    Steps to do a fuzzy lookup in Power Query

    Here’s a detailed overview of how to perform a fuzzy lookup in Excel:

    Step 1: Load your datasets into Power Query. Open Excel and go to the Data tab. Click on “Get Data” and select the appropriate option to load your datasets into Power Query. This could be from a file, a database, or any other supported data source. In this example, I have a couple of tables. One for the data entry, that contains misspellings. And another for the available values that users should have entered:

    Two tables in Excel.

    Step 2: On the data-entry table, select the Home tab and click on Merge Queries.

    The merge queries window in Power Query.

    Step 3: Select the other table and highlight the fields to merge. Leave the default join as a Left Outer and below it, select the option to Use fuzzy matching to perform the merge. Upon doing this, you should see Power Query indicate that it has found more matches.

    Merging tables in Power Query.

    You can also open up the fuzzy matching options to select whether you want the matches to be case-sensitive, and if you want to allow it to match by combining different text parts together. You can also limit the number of matches and set the similarity threshold.

    Specifying the fuzzy matching options in Power Query.

    Once you’re okay with the selections, you can click on OK.

    Step 4: Now, open up the and expand the table that has been merged. This will retrieve the matched values.

    A Power Query table after merging queries.

    If everything is matched correctly, you can go ahead and click Close & Load to get the data back into Excel. If there are issues, you may want to go the previous steps to check your fuzzy matching rules, and perhaps adjust the sensitivity of the matches.

    A Power Query table after merging and expanding the values.

    Using a Transformational Table to help fuzzy matching

    Fuzzy matches don’t always work. In some cases, you’ll need to create a transformational table to help guide Power Query. Here’s an example of when a fuzzy lookup won’t work:

    Two tables in Excel with similar values.

    These names are similar looking and there is a big opportunity for overlap. Even when using a low sensitivity threshold, it only matches 3 of the 6 names:

    Power Query's fuzzy matching only finds three out of six possible values.

    The one way to definitively fix this is to create a Transformational Table for Power Query. What this does is create mapping rules. The table needs to include a ‘From’ column and a ‘To’ column such as this:

    A transformational table in Power Query.

    Now, when you go back to the Merge Queries dialog and adjust the Fuzzy matching options, you can specify that this is the table you want to use:

    Using a transformational table in Power Query when specifying the fuzzy matching rules.

    Having this table will help Power Query understand which values are related to one another. It eliminates the guesswork and can ensure everything is mapping properly. It requires a bit of extra work but it can save time in the long run. Now when using this transformational table, it matches all of the values correctly:

    Power Query matches all the values after using a transformational table.

    If you liked this post on How to Do a Fuzzy Lookup 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.


    Create a Dashboard in Excel to Track Economic Indicators

    Creating a dashboard can be an effective and efficient way to pool in many data points. In this post, I’ll show you how to create a dashboard that factors in several economic indicators, including inflation, interest rates, housing starts, GDP, unemployment, and the performance of the stock market. It will utilize power query and allow you to easily refresh the data.

    Creating and collecting the data points

    To make the data that I’m dynamic, I will also use a variable for the current date, so that the data will automatically update. In this example, it will be called todaysdate which is equal to the following formula:


    Below are the sources for the data that I will use in creating this dashboard along with the Power Query links I will use (along with the variable for the date). I’ll also set up the Power Query links as named ranges in the Excel spreadsheet, making it easy to reference them within the queries.


    Named Range: unemployment


    Power Query:


    Named Range: gdp


    Power Query:

    Interest Rate:

    Named Range: interest


    Power Query:


    Named Range: inflation


    Power Query:

    Housing Starts:

    Named Range: housing


    Power Query:

    Stock Market:

    Named Range: stockmarket


    Power Query:

    Loading the data into Power Query

    Note on named ranges

    Using the links above, I’ll create the connections in Power Query and make adjustments where necessary. To reference a named range in Power Query, you can use the following code as an example:

    NamedRange = Excel.CurrentWorkbook(){[Name="namedrange"]}[Content]{0}[Column1],

    The name is case-sensitive so if you use a named range that is all in lowercase as I have done, then those references also need to be in lowercase in Power Query. However, for the purposes of this example, you don’t need to use named ranges and it is an optional step.

    Creating the Power Query connections

    To create a Power Query connection, I’m going to start by going into the Data tab and selecting From Web under the Get & Transform Data section. For the unemployment rate data, I’ll use the link for that:

    Selecting the URL to create a power query connection from the web.

    After click on OK, I’ll select the table that I want to use, which is the first one on the list:

    Selecting the table to use in Power Query.

    I’ll click on the Transform Data button before loading it. What I will do is split the Month column so that I have both a Month and Year field. To do this, I’ll select the column, right-click and select the option to Split by Delimiter and use a space. I’ll also use this opportunity to put in my named range for the data link. In the Power Query window, under the Home tab, there’s an option to click on the Advanced Editor. Here, I’ll enter my NamedRange variable and use that when referencing the Source:

    Using the advanced editor in Power Query to reference a named range.

    When you’re running a query for the first time, you may see a warning asking you about Privacy Levels. Set these to Public and select Save.

    Setting the privacy levels in Power Query.

    Now it’s time to repeat the steps for the other data sources.

    Transforming the data in Power Query

    There will be some adjustments that need to be made along the way when loading the data. For example, for the data that comes from the FRED website, there are some rows at the top that need to be removed:

    Removing extra rows in Power Query that appear at the top.

    In this case, I’ll need to click the Remove Rows button at the top, and specify that I want to Remove Top Rows and enter a value of 11, to remove the first 11.

    For the housing and inflation data, I need to make additional adjustments since the data is raw and doesn’t show the percent change, which is what I want. Here are the steps I’m going to take for those queries:

    • Unpivoting the data. This is important for the sake of making sure that months are not going across and are instead going vertically. Refer to this post on how to flip and unpivot data in Power Query.
    Unpivoting data in Power Query.
    • Generating previous and current period data. I’ll create a calculated column to calculate the current period and the previous period. After the current period column is created (by simply joining the month and year together), I’ll duplicate the query so that there is an additional table for the inflation data. As for the previous period, this involves subtracting 1 from the year to get the previous year’s values. Then, the year and month are concatenated:
    Calculating the current and prior-year period dates in Power Query.
    • Doing a lookup of the prior-year period. I’ll now merge the query with the one I copied earlier (the other inflation period). This involves doing a lookup of the previous period on the other table’s current period. The goal here is to get the prior-year period’s value. Here’s an overview of how to merge queries in Power Query.
    Merging queries in Power Query.
    • Calculating the percent change. Once the prior-year period’s value is loaded and on the same row, I can create a custom column to calculate the year-over-year change, which is just the new value / old value -1.
    • Removing unneeded values. The final steps involve removing any blank values from the inflation rate and removing and periods that contain the word “HALF” indicating half-year values. Lastly, I’ll split the columns back out so I again have the year and month broken out, this time, along with the inflation rate %:
    Power Query table showing the inflation rate by month and year.

    These steps will be similar for the housing data, except I won’t need to unpivot the data since it isn’t broken out by month and year.

    Creating the pivot tables and linking to the data

    Now that the data is loaded, the next step is to link to it or create pivot tables, to populate the dashboard. For the unemployment data, I will summarize the average by year:

    Pivot table showing unemployment data averages by year.

    For the GDP tab, I’ll pull in just the four most recent quarters. To do this, I can use the INDEX function and the COUNTA function to grab the furthest values. For the most recent period, I can use the following formula:


    For more recent periods, I’ll deduct 1, 2, and 3 from the COUNTA value:

    The GDP growth rates in Excel for the past four quarters.

    The interest rates I will leave as is as that data can chart smoothly given that there normally aren’t many interest rate changes.

    For the inflation rate, I will again take the average annual rate using a pivot table but only looking at data since 2010:

    Pivot table showing the average inflation rate by year.

    On the housing tab, I will break out the average housing starts by quarter, again using a pivot table:

    Pivot table in Excel showing housing starts by quarter.

    Creating the dashboard

    Now that the pivot tables are set up, I can start putting together the dashboard.

    For starters, I’m going to go for a clear, dark background, setting it to black. I’m going to create headers for each of the different categories: Unemployment, GDP, Interest, Inflation, Housing Starts, and Stock Market. I’ll link to the key data, referencing the key metric that I want from each tab. Each header will take up three columns, with a space between each one:

    Key economic indicators showing in Excel.

    What I will also do is create some conditional formatting rules for these values so that they can appear green or red based on their values. Refer to this post for an in-depth overview on conditional formatting. Below the values, I will also extract the date of the most recent data and put it within a formula, to show when the data was last updated:

    Economic indicators in Excel with conditional formatting applied to them.

    Next, I’ll create the charts for the different pivot tables. This is really down to preference and style, but I’ll use a combination of bar, column, and line charts to display the data. Here’s how the dashboard looks after adding a title:

    A dashboard showing economic indicators, using headers and charts.

    And with the data all coming from the web and utilizing Power Query, you can simply just refresh the data to pull the latest numbers, making your dashboard dynamic and easily updateable.

    If you liked this post on How to Create a Dashboard in Excel to Track Economic Indicators, 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.

    H2EInflationRate WEBP

    How to Calculate the Inflation Rate in Excel

    Inflation has been on the rise and it’s a popular topic these days. In an earlier post, I showed you how to create an inflation calculator to determine what something would have cost in a different year. This time around, I’ll go over how to calculate the actual inflation rate in Excel, showing it as an actual percentage. Let’s get started.

    How do you calculate the inflation rate?

    If you follow this link to the U.S. Bureau of Labor Statistics, you’ll see a table that has all the latest inflation data. These are indexed values and so to calculate the inflation rate, all you really need to do is take the current index value and divide it by the prior year’s data. For example, September 2022’s inflation rate was 8.2%. To arrive at that, you can simply take the September 2022 index value of 296.808 and divide that by 274.31, which is the value from September 2021. The result is 1.082.

    You could download this table into Excel and do a series of lookups to do these calculations. But that’s effectively what I did in the previous post. This time around, I’m going to make this much more automated and involve Power Query.

    Download the data using Power Query

    First, go to the Data tab in Excel, and click on the From Web button next to Get Data. Then, paste the URL from the link that has the inflation data. Then, you’ll see the Navigator page for Power Query, where you can select from the different tables that are found on the webpage:

    Selecting a table from the Power Query Navigator window.

    Table 1 is the one that contains the inflation data organized by month and year. Before loading this into my spreadsheet, I’m going to make some adjustments so that I can easily calculate the inflation rate right within Power Query. To do that, click on the Transform Data button.

    Modifying the data in Power Query

    By default the data shows in the following format:

    Inflation data loaded into Power Query.

    I want the years and months to both be in columns. That means I need to flip the months. To do this, I’ll right-click on the Year header and select Unpivot Other Columns. And then voila, my data is in a more manageable format:

    Power Query table after unpivoting headers.

    Since I want to compare to the previous year, I’ll also create another column for the previous year. To do this, I can just select the Year column and in the Add Column section, click on the Standard icon that shows different mathematical operators and select Subtract:

    Using the standard button in Power Query to quickly apply calculations to a field.

    The next screen will ask me for the number I want to subtract from each value, which will just be 1. After entering that and clicking OK, I will have a new field, which I will re-name to PreviousYear.

    Next, I’m going to create a combined field for the year and month. First, I’ll convert the Year and PreviousYear fields to text. This can be done by clicking on the icon in their headers and selecting Text.

    Then, under the Add Column section, I’m going to select Custom Column. From there, I can enter the following formula to concatenate the fields:

    Creating a custom column in Power Query to join year and month.

    I’m going to repeat these steps for the PreviousYear, and that column I’ll call the Previous Period.

    Next, I’m going to split this table into two. I’ll rename the current table to Current. Then, I’m going to create a copy of it by right-clicking and selecting Duplicate, and I’m going to call the new table Previous. In the Current table, I’m only going to leave the following columns: Current Period, Previous Period, and Value. While on the Previous table, I’m only going to include the Current Period and the Value.

    What I will do next is to merge the tables, where I’ll be looking up the prior period’s value. To do this, I’ll go to the Current table and under the Home tab, select the option to Merge Queries. I’m going to connect the queries with the Previous Period from the Current table to the Current Period in the Previous table. The goal here is to be looking up the prior-year period:

    Merging tables in Power Query.

    Next, I’ll click on the icon in the newly created column to expand the fields out:

    New field created in Power Query after merging queries.

    I’m only going to select the Value, as that is the prior value that I want. I’m going to rename that field the Prior-Year Value:

    Power Query table after merging queries and extracting the prior-year period value.

    These values shouldn’t be the same and you can easily check to make sure that they are correct. For example, 1914-Jan will show up in both the Current and Previous Periods. And those corresponding values both show 10. Now that I’ve got the current and previous values, the next step is to calculate the percent change. To do this, I’ll create another Custom Column, with this as my calculation:

    Calculating the inflation rate in Power Query.

    I’ll then convert the field so that it is in a percentage format, and here is what my inflation rate column looks like:

    Power Query table with the inflation rate calculated.

    The only thing left at this point is to clean up the Power Query table to remove any unnecessary columns and values. I will filter out any values that are empty. To do this, I’m going to click on the drop down for the Inflation Rate header and select Remove Empty. This will get rid of the values that didn’t have a prior year value. I’ll also remove all the columns except for the Current Period and the Inflation Rate.

    Power Query table showing only the current period and the inflation rate.

    Now that my transformations are complete, I can load this into Excel using the Close & Load button on the Home tab. It will load the Previous table as a new tab, but I can delete that so that I only have the Current table.

    If I go to the bottom of my table, I can now see the latest values:

    Inflation table loaded from Power Query into an Excel spreadsheet.

    September’s inflation rate was 8.2%, which is correct. Now, moving forward, as the inflation rate data updates, I can just right-click on this table and click Refresh, and Power Query will load the data and calculate the latest inflation rate for me.

    If you liked this post on How to Calculate the Inflation Rate 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.


    Import Multiple Stock Tickers Into Excel Using Power Query

    Power Query can allow you to easily import data from another spreadsheet. But did you know that you can load multiple files from a folder at once? All you need to do is load the files you want to import into a folder, and Power Query can do the rest. In this article, I’ll show you how you can do this with stock prices and how you can import multiple ticker files from Yahoo Finance into Power Query at once.

    Put all the files into a single folder

    Whatever type of files you want to import, the key thing is that their format is consistent. This is because Power Query will follow a similar process when importing them. If, for example, you always remove certain columns from a file, then you want to make sure that every file you import has those columns. If there’s a discrepancy, then Power Query may struggle to load the files properly.

    In this example, I’m going to use CSV files from Yahoo Finance. Let’s say I want to download data for multiple stock tickers. If I go to Apple’s stock ticker page, there’s a link to download the latest stock prices. In a previous post, I went over how to download stock prices for a single ticker. This time around, I’ll show you how you can do it for as many as you want. If I want to download multiple tickers, I’ll start by downloading all the different CSV files for them and putting them into just a single folder:

    Folder with CSV files for different stocks.

    Here I’ve got multiple tickers downloaded, including Apple’s. This is now the folder I will reference when extracting the data from Power Query.

    Importing the files Into Power Query

    In Excel, the next step is to simply download the data. Under the Data tab, click on the Get Data button and select the option for From Folder:

    Selecting to import files into Excel from a folder.

    Then, navigate to the folder where your files are stored and click on Open. Now the Power Query window will load and you should see something like this:

    Power Query window showing all the files in a specific folder.

    Here I see all the files from my folder. There are three different options I can take at this point:

    • Transform Data. Clicking on this option will allow me to transform the table above.
    • Load. If I don’t want to make any transformations and just load the table above, this is what I’ll select. But like the above option, this will not combine the data, so this is not what I want.
    • Combine. This is the option that I will choose as it will combine all these files together. From here, you’ll have the option to Combine and Transform or to just Transform and Load (e.g. if you don’t need to make any adjustments).

    On the next screen, you can click on OK and the combined data will be loaded. To make the process as seamless as possible, you’ll want to ensure that your files follow the same format. Otherwise, it can be more difficult to get the desired results.

    After clicking on OK, now the data loads, and all my stock data from Yahoo Finance is downloaded, with all the different tickers:

    Multiple stock files downloaded into Excel.

    Now, you can add more downloads from Yahoo Finance for different tickers, put them in the same folder, and then just refresh the query. Your spreadsheet will now automatically update based on the CSV files within the folder.

    If you liked this post on How to Import Multiple Stock Tickers 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.


    How to Pull Crypto Prices and Data Into Excel

    In this article, I’ll show you how you can download the latest prices for cryptocurrencies, along with percent and volume changes. The data will be downloaded via an API from Once you’ve set up the API, it becomes a breeze to pull crypto prices and data into Excel, in just a matter of seconds.

    Getting an API Key

    One of the first things you’ll want to do is go onto the website where you can request an API key, which you’ll need if you want to query the data. Once you have the key, you can begin pulling in values. You don’t need to worry about saving or remembering your API key because once you’re logged into the site, you’ll see an Overview section that shows you where you can copy your API key by hovering over that section. On this page, you will also see how many credits you have used today and this month versus how many are available on your plan.

    Overview page on

    Setting up the connection in Power Query

    Once you’ve got your API key copied, you can go into Excel and create a Power Query connection. To do this, go under the Data tab and select the From Web button:

    Creating a Power Query connection.

    Then, you’ll enter the URL for the API connection, which is On the documentation page, you’ll also see a list of possible endpoint paths. Under the basic plan, not all endpoints will be available. In this example, I’m just going to retrieve the latest market data. And for that, the URL is as follows:

    I’ll put that into the Power Query URL. However, because the connection requires authentication, I need to check off the option for Advanced rather than just leave the default to Basic. In the section for HTTP request header parameters, you need to enter X-CMC_PRO_API_KEY (you’ll find this on the documentation page) and your API key. Here’s how that looks:

    Entering the headers and API data for Power Query to connect to

    Then, click on OK and Power Query will go to work on creating your connection.

    Formatting the data in Power Query

    Once loaded into Power Query, you’ll see this:

    The Power Query window after creating the connection.

    If you click on the List button next to data, then you will get a series of records:

    A list of the records in the Power Query source data.

    On the top-left-hand corner, there is an option to convert this To Table. Click on that button, leave the default options on the next window as they are, and then click on OK. We’re still left with a long list of records. For this step, click on the icon highlighted below, at the top of the column:

    Clicking on the option in Power Query to open up the records into fields.

    When the next screen pops up showing you all the columns that will be expanded, click OK. Now you have something that looks a lot more useable:

    Power Query table with columns expanded.

    But there’s still more information that can be extracted. Scroll over to the last column, which should contain the word ‘quote’ in its name. Here there will be a list of records again. And using that button at the top of the field, this can also be expanded. It only has a USD field and once expanded, it looks like nothing has changed. Click on the header button once more, and now you’ll see fields showing volumes and price changes.

    Expanded Power Query table showing more columns.

    Now, you can load the data into Excel by clicking on the Close & Load button. You should now see it populate in your spreadsheet:

    Power Query table loaded into Excel.

    Now you can do a refresh at any point in time and your query will pull in the latest data from

    If you liked this post on How to Pull Crypto Prices and Data Into 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.


    How to Flip a Table in Power Query

    Do you want to change how your Power Query table looks? In this post, I’ll show you how you can flip your data so that you can turn a table that looks like this:

    Table in Power Query that has order numbers going vertically and other fields across.

    Into this:

    Table in Power Query that has order numbers going across and other fields going vertically.

    In the second table, it’s a bit easier to see all your fields vertically and you don’t need to scroll across to see them all. Depending on how you may want to visualize your data, you may find it useful to swap from one view to the other.

    How to transform and flip data in Power Query

    To transform the first field into the second field, you’ll need to take two steps in Power Query. The first is to unpivot your data. In this example, I want the order numbers to be as my headers going across, and so I will right-click on that header and select the option to Unpivot Other Columns:

    Unpivoting other columns in Power Query.

    That will result in the table transforming as follows:

    Power Query table after being unpivoted.

    This isn’t quite what I need yet, but it’s close, as it has the fields going vertically instead of horizontally. The last part is to put the order numbers going across the top. To accomplish this, I will select that column and choose the Pivot Column option to re-pivot the data again.

    The Pivot Column option in Power Query.

    Then, on the next screen, it’s important to select the correct values option. And, you’ll also want to select Advanced options and choose Don’t Aggregate:

    Pivot column options in Power Query.

    Now, I end up with a Power Query table that has been flipped and has the order numbers going across and the fields going down vertically:

    A table in Power Query that has been flipped from its original layout.

    How to flip the data back

    Let’s suppose that you start with the above table and you want to flip it the other way (so that the attributes are going across). Here again, we’ll start with unpivoting the data back. The most important consideration is to know which field you want going across. In this case, it’s going to be the attribute field. Right-click on that and click on Unpivot Other Columns. Then, you’ll see this table:

    An unpivoted Power Query table.

    Now, the next step is to re-pivot the data. Select the Attribute field on the left and click Pivot Column. Again, you’ll need to select the correct value column and choose the option so that you Don’t Aggregate:

    The Pivot Column settings in Power Query are displayed.

    And now we’re back to having the order numbers going vertically. Although some of the fields have moved around amidst all those changes, the format is back to how it was at the beginning:

    Power Query table showing order numbers going vertically and other fields going across.

    If you liked this post on How to Flip a Table 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.


    Creating a Stock Market Dashboard in Excel

    Want to create a dashboard to track the stock market and the latest business-related news? Below, I’ll show you how you can create a stock market dashboard using Excel and Google Sheets to pull in all the data you’ll need. If you’d prefer to just download the file, you can do so here.

    Step 1: Compiling the data

    You can get stock prices into Excel using the STOCKHISTORY function. However, that isn’t available on older versions of Excel and it also doesn’t pull in the current day’s prices. Using Google Sheets can be more effective for this purpose. Plus, on there, I can pull in business-related news as well.

    To start, I’m going to pull in values for the Dow Jones, Nasdaq, and S&P 500. I’ll also download the values of a couple of exchange-traded funds (ETFs) that track healthcare and tech stocks. To get the latest price, you can use the built-in GOOGLEFINANCE function that’s only available on Google Sheets. To get the latest value of the Dow Jones, the following formula will work:


    And to calculate the percentage change:


    For the Nasdaq, you’ll use “.IXIC” and for the S&P 500 the ticker is “.INX”

    For the ETFs, since they aren’t indexes, there is no period beforehand and I reference XLK for tech and XLV for healthcare. In my Google Sheets file, I have a simple layout for the values and their changes that I will later pull into Power Query:

    Stock market indicators in Google Sheets.

    Next, I’ll also download the latest business-related news. Google Sheets has another unique function for this: IMPORTFEED. All you need to do is find an rss feed from a website that you want to pull information from. Not every website has an rss feed but what you can do is just do a Google search for the name of a source and ‘rss’ to see if you can find a link. There are three sources I’m going to use for this dashboard:




    I will pull them all in the same way, using the IMPORTFEED function. Here’s an example with the CNBC feed:


    In Google Sheets, the top articles from each of those rss feeds will show up, including the title, URL, date created, and even a brief summary:

    News articles pulled into Google Sheets using the IMPORTFEED function.

    Now, it’s time to pull all this data into Excel.

    Step 2: Loading the data into Excel using Power Query

    To import data from Google Sheets into Excel, you need to first share the sheet. While in Google Sheets, go into File -> Share -> Publish to web. Then, you’ll be prompted to select what you want to share. I’ll start with the Markets tab I created and then the News tab:

    Publishing data to the web from Google Sheets.

    Copy this URL as you’ll need it to load the data into Power Query. While you’re back in Excel, go under the Data tab and click on the From Web button under the Get & Transform Data section. You’ll be prompted to enter a URL. This is where you’ll paste the link that you copied from Google Sheets:

    Creating a query in Excel using the from web option.

    On the next page, select Table 0 as where you want to extract data from. And if you want to do some cleanup (getting rid of extra columns), you can do so by clicking on the Transform Data button:

    Selecting a table for Power Query to pull data from.

    To remove any unneeded columns in Power Query, just right-click on a column header and click Remove:

    Removing a column from Power Query.

    Once you’re done, click on the button to Close & Load if you want the data to be loaded on a new sheet. If you want to control where it gets pasted, then use the drop down and select Close & Load To.

    Repeat these steps for the other Google Sheets tab.

    In addition, I’m also going to load data from a few other sources:

    Top 100 Gainers on Yahoo Finance:

    Top 100 Losers on Yahoo Finance:

    Upcoming IPOs from IPOScoop:

    The process for importing these links into the dashboard is the same as for Google Sheets. Go through Power Query, import from web, and paste in the URL plus make any formatting changes necessary. The next step involves putting all this data together in a dashboard.

    Step 3: Creating the dashboard

    In my spreadsheet, I’ve created two tabs: one that hold all my Power Query downloads (the ‘Data’ tab) and a ‘Dashboard’ tab for where all the information will be displayed.

    To make the set up of the dashboard easy to manage, I’m going to change the column width to 10 for everything. To do that, press CTRL+A to select all the cells on the Dashboard tab, then right-click on any of the headers, and there you’ll be able to select column width.

    First up, I’m going to get the indexes and market indicators as a starting point. To do this, all I need to do is link to the values and the percentages for the S&P 500, Dow Jones, Nasdaq, Tech, and Healthcare tickers I imported from Google Sheets. By default, I’ll set the formatting for all the cells to be green:

    Market indicators imported into Excel from Google Sheets.

    To make this more dynamic, I will add some conditional formatting so that if the percentage change is negative, the corresponding cells will highlight in red. For this, I can select all the cells in green above and create a conditional formatting rule the starts with where the first percentage is (in my spreadsheet, it is cell E6):


    This is a simple rule but by not freezing the column (E) and freezing only the row (6), it can be applied to all the cells above. I can apply a red background color so that if any of the percentages are negative, the cells will highlight accordingly:

    Market indicators imported into Excel from Google Sheets with negative values showing up in red.

    For the next part of the dashboard, I will copy over the news stories that were also downloaded from Google Sheets. This time, I’m going to use the HYPERLINK function so that I can not just link to the title but also create a clickable link that will allow me to open the story should I want to open it in my default browser. The function itself is simple and involves just two arguments, one for the actual URL and another for what the text should show up. Since it’s shorter, I’m going with the title. After applying some formatting and copying all three sources, this is what my dashboard looks like:

    Stock dashboard showing stock market indicators and the latest business news.

    For the last part of the dashboard, I’m going to pull in the tables from the other data sources (top 100 gainers, losers, and upcoming IPOs). If these are on the Data tab, you can just cut and paste them onto the Dashboard tab. And for each one of the tables, I’m going to create a chart based on the symbol and the percent change.

    To do this, select the Symbol column and the % Change columns. Then under the Insert tab in Excel, open up the charts and select Treemap. If you selected too many columns or didn’t specify which ones you wanted, you might get a different look. But if you only selected those two, you should see something like this:

    Treemap chart in Excel.

    Since the chart includes the symbols, the legend can be deleted. Also, I’m going to change the color scheme so that it goes from dark green to light green. This change can be made by clicking the Change Colors button next to the chart:

    Changing the color scheme of a treemap chart.

    To add the percentage to each of the boxes, right-click on one of the ticker symbols and click Format Labels. Then, check off the box for value so that the percentages will also show up next to the symbols:

    Treemap chart in Excel showing ticker symbols and percent changes.

    These steps can be repeated for the other charts. However, for the losers table, since the percentage change is negative, it needs to be flipped to positive first. To do that, that query needs to be edited. If you click on Queries & Connections section under the Data tab, you’ll see a list of all your queries. Click on the one that takes you to the top losers query. Right-click edit and Power Query will open up.

    Once in Power Query, select the % Change column and under the Transform column at the top, click on the Standard drop down, which will show you all the different calculations you can apply:

    Power Query menu showing standard calculation operators.

    Click on Multiply and then for the value in the next box, enter -1. Pressing OK will then flip all the values to negatives.

    Multiplying values in Power Query.

    Now, you can create the same Treemap chart for this table. For the IPOScoop download, the field I’m going to use is Est. $ Volume. This query will also need to be edited in order to use that field since it is text. Although it is a bit more complex since this field contains text and dollar signs, there’s a relatively easy way to parse out what you need.

    In Power Query, select the column, and under the Add Column tab, click on the Column From Examples button (choose the option for From Selection):

    Column from Examples button in Power Query.

    That will create a new column:

    Power Query editor after adding a new column from examples.

    In Column1, I can enter the value that I want Power Query to extract. If I just enter a few values to show what I want (in this case, I only need to enter 300), Power Query fills in the rest, figuring out what I am trying to do. It’s an easy way to parse data in Power Query.

    The Power Query column from examples filling in the rest of my values.

    After creating the new column, I can change the format from text to currency by clicking on the ‘abc’ letters in the title:

    Changing a column's format in Power Query.

    Now that I have the column created, I can remove the original one and load the data back into Excel and proceed with making a Treemap for this chart using the symbol and the newly created column.

    The last thing I’m going to do is create a new column to show the change in volume to determine how much more (or less) trading there was for each stock on the day compared to the average. This will compare the average three-month volume with the current day’s volume. The one complication is that some of the values contain letters:

    Stock trading volumes showing letters and numbers.

    To convert these values, it’s important to first parse out the letters. If a value doesn’t contain a letter, then it is in thousands. I’m going to set everything to millions. So if the value doesn’t contain a letter, it will be multiplied by 0.000001 to convert it into a fraction of a million. And if it contains a ‘B’, it will multiply by a factor of 1,000. Otherwise, the value will remain as is. Here’s how the first part of the formula will look like, which involves determining the multiplication factor:


    Since the letter is always at the end of the string, just using the RIGHT function (which looks at the right-most string) will suffice. This result needs to be multiplied by the remaining value. That value can be extracted by using the SUBSTITUTE function which will replace one value with another:


    In the above formula, the value of B will be replaced with an empty string. This is the same as simply removing the value. To ensure that any ‘M’s are also removed, I will embed this formula within another one that will substitute out those values:


    I multiply this by the first part of the formula, and my numerator is as follows:


    For the denominator, I’m going to use the exact same formula, except instead of the current volume, I’m going to use the field for the three-month average:

    IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))*SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””))

    The last part involves putting all this together:

    =(IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)/(IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””)))-1

    The -1 at the end is to put the change in a percentage of less than 100%.

    Another step you might consider at this point to help identify these changes is to format these numbers so they are easier to read. You can use conditional formatting (color scales) to easily highlight the highs and lows. And if you want to format the percentages so that they show commas and negative percentages show up red, use the following in the custom number format:


    The semi-colon before the [Red] separates out what the percentages should look like when they are positive (the part before the semi-colon) and what they should like when negative (the part that comes afterward). The [Red] text indicates the value should be in red text.

    Here’s how this section looks as part of my dashboard:

    Stock market dashboard showing top and bottom gainers.

    And here’s a snapshot of the dashboard as a whole.

    Stock market dashboard in Excel.

    One thing to remember: if you want to update the queries and the dashboard, make sure you go under the Data tab and click the Refresh All button. Otherwise, your data may not be up to date.

    Also, to prevent your tables from stretching out when updating the queries, select each one of them and under the Table Design tab, click the Properties button (under the External Table Data section), where you should see this:

    External data properties in Excel.

    Make sure the Adjust column width checkbox is unticked. This will prevent your columns from stretching out and disrupting your layout.

    If you liked this post on Creating a Stock Market Dashboard 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.