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

Source: https://www.bls.gov/charts/employment-situation/civilian-unemployment-rate.htm

Power Query: https://www.bls.gov/charts/employment-situation/civilian-unemployment-rate.htm


Named Range: gdp

Source: https://fred.stlouisfed.org/series/A191RL1Q225SBEA

Power Query: https://fred.stlouisfed.org/graph/fredgraph.xls?drp=0&fo=open%20sans&mode=fred&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&id=A191RL1Q225SBEA&cosd=1947-04-01&coed=2022-12-07&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Quarterly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-12-07&revision_date=2022-12-07&nd=1947-04-01

Interest Rate:

Named Range: interest

Source: https://fred.stlouisfed.org/series/DFEDTARU

Power Query: https://fred.stlouisfed.org/graph/fredgraph.xls?bgcolor=%23e1e9f0&drp=0&fo=open%20sans&mode=fred&recession_bars=on&ts=12&tts=12&nt=0&thu=0&trc=0&id=DFEDTARU&cosd=2008-12-16&coed=2022-12-07&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Daily%2C%207-Day&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-12-07&revision_date=2022-12-07&nd=2008-12-16


Named Range: inflation

Source: https://data.bls.gov/timeseries/CUUR0000SA0?years_option=all_years

Power Query: https://data.bls.gov/timeseries/CUUR0000SA0?years_option=all_years

Housing Starts:

Named Range: housing

Source: https://fred.stlouisfed.org/series/HOUST

Power Query: https://fred.stlouisfed.org/graph/fredgraph.xls?bgcolor=%23e1e9f0&drp=0&fo=open%20sans&mode=fred&ts=12&tts=12&nt=0&thu=0&trc=0&id=HOUST&scale=left&cosd=1959-01-01&coed=2022-12-07&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-12-07&revision_date=2022-12-07&nd=1959-01-01

Stock Market:

Named Range: stockmarket

Source: https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC

Power Query: https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC

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 coinmarketcap.com. 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 https://coinmarketcap.com/api/ 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 coinmarketcap.com.

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 https://pro-api.coinmarketcap.com. 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 coinmarketcap.com

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 coinmarketcap.com.

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:

CNBC: https://www.cnbc.com/id/10001147/device/rss/rss.html

WSJ: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

NYT: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

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: https://finance.yahoo.com/gainers/?offset=0&count=100

Top 100 Losers on Yahoo Finance: https://finance.yahoo.com/losers?offset=0&count=100

Upcoming IPOs from IPOScoop: https://www.iposcoop.com/ipo-calendar/

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.


How to Import Financial Statements Using Power Query

In this post, I’ll show you how you can import a company’s financial statements into Excel using Power Query. Previously, I’ve covered how to get stock prices from both Yahoo Finance and Google Sheets. But to get financial statement information, I’m going to use a different source: wsj.com. The reason being, is it’s in an easy format to export and that makes the import process very easy for Power Query.

Downloading the data

I’m going to use Walmart’s financials for this example. And if you navigate to the following URL, you will get a summary of Walmart’s quarterly financial statements:


What’s convenient about this URL is that it contains both the ticker, the statement type, and indicates that the financials are quarterly. That makes it easy to alter in case you wanted to look for annual statements or a balance sheet rather than an income statement. Just changing the URL will get you to the right page. The above link is what I’m going to use for this example.

To load the data into Power Query, go to the Data tab and click on From Web:

The data tab in Excel that shows the Get & Transform data section.

Then, paste the URL in the following box:

Entering a URL in the From Web section.

After clicking OK, you can select which table to import. In this case, it’s going to be Table 0:

Selecting which table to import from a Power Query import.

Next, press the Transform Data button to make changes before it gets imported. I’ll start with removing the column at the very end, showing the trend, as it doesn’t contain any information. To remove it, right-click on the header and click Remove:

Removing a column from Power Query.

I’m also going to remove the Changed Type step, which automatically changes the data types. To get rid of the step, click on the X next to the step:

Removing a step from Power Query.

This is important because since the header names change based on the quarter, it isn’t going to be helpful to have this step since it looks for hardcoded values. An optional step you could take is to Demote Headers so that the header names are generic and not tied to a specific quarter. However, this isn’t necessary if you remove the Changed Type step. For more information on changing header names, refer to this post.

Once you’re done making changes, click on Close & Load in the top-left corner, and then your data will load into a sheet.

Close & Load button in Power Query.

The download will work just fine right now. However, let’s also make the file a bit more versatile in case you want to quickly change the ticker symbol.

Setting up the variables

First up, I’ll create a named range for the ticker symbol, called ‘Ticker’ :

Power Query table with a variable for a company's stock ticker off to the right.

I’ll now go back into the query editor to account for this named range. To edit a query, go into the Data tab, click on Queries and Connections, and then off to the right you should see your queries. Right-click edit on the one you want to adjust:

Selecting the option to edit an existing query in Excel.

Then, click on the Advanced Editor button near the top of the Power Query window:

The Advanced Editor button located on the Power Query Home tab.

I’m going to add the Ticker variable under the let section as follows:

Ticker = Excel.CurrentWorkbook(){[Name=”Ticker”]}[Content]{0}[Column1],

Note that Power Query is case-sensitive and you will get an error if what you’ve entered doesn’t match exactly what you’ve set as your named range. Also, make sure to add a comma at the end.

I will also need to adjust the Source variable so that it uses the Ticker variable:

Source = Web.Page(Web.Contents(“https://www.wsj.com/market-data/quotes/”&Ticker&”/financials/quarter/income-statement”)),

The key thing here is to break up the part of the URL that mentions WMT and replace it with the named range. Here’s what the code looks like within the Advanced Editor:

Power Query code in the Advanced Editor.

Now, you can Close & Load back into the worksheet. To test the named range, what you can do is replace the ticker value from WMT to AMZN, and if it works correctly, it should load Amazon’s income statement instead. After changing the ticker symbol, remember to press the Refresh All button under the Data tab:

The Refresh All button in the Data tab.

If it works, you should see a whole new set of data populate on your spreadsheet:

Amazon's income statement loaded into an Excel spreadsheet using Power Query.

If you liked this post on How to Import Financial Statements 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 Foreign Exchange Rates Into Excel Using Power Query

In a previous post, I showed how to get stock prices using Power Query. This time around, I’m going to show you how we can do the same for foreign exchange rates, pulling that data into Excel. For this example, I am going to use the currency conversion site xe.com.

Creating the connection

The first thing that we need to do when creating a Power Query connection is determining which web page the data will come from. On the xe.com website, there is a currency table for each day. If I wanted to pull the USD foreign exchange rates into Excel as of August 22, the URL I would use is as follows:


The link is convenient because I can easily alter the currency and the date. I’ll go over how to do that later but first, let’s create the connection. To do that, go in the Data tab on the Ribbon and click on From Web button under the Get & Transform Data section:

Get and Transform data section of the Excel ribbon.

Then, on the next screen, I’ll paste the link and click OK:

Entering in the URL for Power Query to pull data from the web.

Once it has loaded, there will be multiple tables to choose from. Table 0 is the one that has the exchange rates:

Selecting the correct table from the Power Query download.

Instead of clicking to load the data, click on Transform Data to make any adjustments to it before it loads into Excel.

Adjusting the query

Once the query is loaded, you’ll see the following window:

Power Query window with foreign exchange rates.

You probably don’t need or want to see all these rows in Excel. So what you can do before loading it is to clean the data up a bit. Let’s say I only want to pull the US exchange rates in Excel for EUR, GBP, CAD, and AUD. To do that, I’ll click on the Currency header and filter for just those options. You can filter the same way you would an Excel table. And once you’re done, you should see the table get a whole lot smaller:

Power Query table after filtering currency values.

To cut down the table even more, I can remove the Name field since. To remove any column, simply right-click on it and click Remove:

Removing a column from Power Query.

Clicking on the Load & Close button will now populate this into my Excel sheet:

Foreign currency rates loaded into Excel.

Now, let’s alter the URL so that it can be adjusted dynamically for both the currency and date.

Using variables in the Power Query link

An advantage of using a link that has the currency and the date in it is that it is easy to change. I’m going to start by creating a couple of variables. The first is for the currency, and the second is the date. I’ve set up named ranges of ‘Currency’ and ‘Date’ the following fields:

Named ranges in Excel for Currency and Date.

When entering the date, I’m entering an apostrophe (‘) first so that formatting isn’t an issue and Excel reads the value as text.

What I’m going to do next is go into Power Query and create these variables. To do so, go back into the Data tab and click on Queries & Connections. Off to the right, you should see your query. Right-click on it and select Edit:

Editing a query in Excel.

This will open Power Query back up. I’m going to click on Advanced Editor button on the Ribbon, under the Query section:

The query options in the Power Query Home tab.

At the top of the code, I’m going to insert two lines. One for each variable:

Currency = Excel.CurrentWorkbook(){[Name=”currency”]}[Content]{0}[Column1]

Date = Excel.CurrentWorkbook(){[Name=”date”]}[Content]{0}[Column1]

I will also need to adjust the Source, so that instead of this:

Source = Web.Page(Web.Contents(“https://www.xe.com/currencytables/?from=USD&date=2021-08-22”))

It becomes this:

Source = Web.Page(Web.Contents(“https://www.xe.com/currencytables/?from=”&currency&”&date=”&date))

The variables are ready to go. But before using these them, it’s important to remove the Changed Type step from Power Query:

Removing the changed type step from Power Query.

This step looks for exact column names which can cause an error if your names change when downloading data.

Now with that done, you can change the values and your table will change. Here’s what it looks like if I change the currency to AUD and set the date to July 31:

Excel table showing different foreign exchange rates.

The one problem, however, is that now I have AUD and don’t have USD. What I can do is create a separate table of all the currencies I want to pull in:

List of currencies in Excel.

I can load this table into Power Query by going back into the Data tab and this time clicking on the From Sheet option. I’ll rename the FXtable and it shows below the other table:

FXtable created in Power Query.

Now, if I only want to see the values from FXtable, what I will need to do is merge the queries.

Merging queries in Power Query

Switch over the Table 0 query and get rid of the Filtered Rows step. Then, on the Home tab, there is an option to Merge Queries in the Combine section that you’ll want to click:

The combine section in Power Query.

On the next screen, I’ll select the FXtable from the drop down and select the currency fields from each table:

Merging tables in Power Query.

Leave the default of Left Outer selected and then click OK:

Power Query after applying merge tables.

Next, click on the button on the FXtable to expand the table:

Power Query button to expand table.

Click OK on the next section to expand the only field from that table:

Selecting which fields to expand in Power Query.

Which will result in this:

Power Query after expanding the table.

Then, just filter the FXtable.Currencies field so that null values don’t show up, and you’re left with just the currencies that were present on that table:

Filtering out the null values in the FXtable.Currencies column.

Now that the table has served its purpose, I can remove the FXtable.Currencies column and I’m back to what I had before:

Power Query after removing the FXtable.Currencies column.

Now, I can modify the base currency, the date, plus the currencies that I want to show up. Suppose I just want to see the EUR-GBP currency rates for Jan 1, I could enter the following values:

Spreadsheet showing named ranges and the foreign exchange rates.

All you need to do is refresh the data from the Data tab and all the queries will update:

The Refresh All button in Power Query.

And the data matches what comes from the xe.com site:

The Foreign Exchange rates showing in Power Query.

Are you looking for a way to pull historical data for a currency pair? You can use Yahoo Finance to do that, and the steps for doing that query is similar to how you would pull stock quotes from there (refer to the link at the top of this post).

If you liked this post on How to Pull Foreign Exchange Rates 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.


Dealing With Tables With Changing Headers in Power Query

One of the more common errors you’ll run into when using Power Query is when your headers change names. If that happens, and you have a step in Power Query that alters the headers or relies on them in any way, you’ll end up with an error saying a header wasn’t found. This can happen if you are querying a table whose headers change or if you simply change the name of one of them yourself. Below, I’ll show you an effective strategy for dealing with tables with changing headers in Power Query so that even if the header names change, you can avoid running into these errors.

Changing headers in Power Query

Let’s start with the basics of how you would normally create headers in Power Query. For this example, I’m going to use data from the City of New York Expenses, which you can download from here. This is what the data set looks like:

City of New York Expenses in Excel format.

To launch this data into Power Query, go to the Data tab and click on From Sheet. Once the range is selected, click on OK. Once in Power Query, the data looks as follows:

Expense data that is populated in Power Query.

To change any header name in Power Query, all you need to do is double-click on the header. In this example, I’m going to change the first header so that rather than Publication Date, it will just say Date. Then, I’ll go in and click Close & Load to get out of Power Query.

The problem arises if the header in the table were to change. If I go back into the Expense_Actuals sheet (which Excel automatically created for me when I set up the table in Power Query), it shows these headers:

Header names in the expense table.

And if I change the first header so that it looks like this:

Table headers after changing the name of the first header.

I’m going to have a problem, because Power Query is going to be looking for Publication Date rather than just Date. Now, if I go under the Data tab and click Refresh All — which will update the query — I get the following error:

Power Query error showing that the column wasn't found.

This is the error that shows up when Power Query can’t find the header. If I modified the header name for Fiscal Year or any other column, then I wouldn’t get this error. The reason is the step of changing Publication Date to Date is hardcoded into Power Query and if it doesn’t find that header, it will give me the above error message.

The key to dealing with tables with changing headers in power query is to demote them, which I’ll cover next.

Promoting and demoting headers in Power Query

By default, Power Query will use the first row of your table as its headers. In my situation, this is a problem because if the header in the table is changing, I could run into errors. To eliminate this, I’m going to demote the headers. To start, I’ll go back to edit the query. To do this, you can go under the Queries & Connections table, right-click on the query and click on Edit:

Selecting the option to edit the query.

Then, select the first Power Query step (which will likely just be ‘Source’), and you should again see the table again:

Table in Power Query.

Then, under the Transform tab, in the Table section, click on the option to Use Headers as First Row:

Select the use headers as first row option in Power Query to demote headers.

This has the effect of demoting the headers so that they are now rows. And upon doing so, the header names become just Column1, Column2, etc:

Power query table after demoting the headers.

The option above it to Use First Row as Headers is the exact opposite — it would promote the first row and make that the header for all the columns; it would undo the above step.

However, now with the plain column names, what you can do is go through and re-name each of the headers however you want, which now looks as follows:

Power Query table after changing the header names.

The one issue that remains here is that now we have the old header names in the first row. To get rid of them, go into the Home tab and click on Remove Rows and select the option to Remove Top Rows

Removing the top rows from Power Query.

And then for the number of rows to remove, just enter 1 and click OK:

Removing the first row from the Power Query table.

And then, the updated Power Query table looks as follows:

You can remove any other steps that were previously in Power Query to avoid it looking for the old header names.

One thing to also remember is the Changed Type step which Power Query automatically generates and looks to convert each header into the correct data type. As you can see from the formula for that step, it will look for the hardcoded header names:

The changed type in Power Query also looks for exact header names and can cause issues as well if you haven't removed this step.

You can either modify the header names within the formula, or you can just remove the step entirely. But this too can cause issues if it is looking for a hardcoded header name. Once you’ve made the last step of removing or changing the Changed Type step, you should be good to go.

Now, when you go and refresh your query, even if you have modified the header names, your data will refresh correctly and put in the header names you have specified in Power Query.

If you liked this post on dealing with tables with changing headers 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.