Power Query can help you summarize and analyze your data. But did you know that you can also add calculated columns similar to how you might in a pivot table? In this post, I’m going to show you how you can compare your values against an average to show a percent change in a new calculated column within Power Query.
This is the table that I am going to work with for the purpose of this calculation:
Calculating an average from a column in Power Query
Before I create my calculated column, I’m going to create an average that I can compare against in the Price field. Here’s how I can calculate an average:
1. Create a new step in Power Query. This can be done by clicking on the fx button next to the formula bar. This will create a new step which will reference your most recent step automatically. Since my first step was just the Source that is what my new step will refer to:
2. Use a List function to perform a calculation on a column. In Power Query, there are many List functions you can use to do calculations. In this example, I want to calculate the average based on the Price field. So my formula will be as follows:
= List.Average(Source[Price])
I have wrapped the Price field within the step that I was referencing (Source) and used that as the argument in my List.Average function. There are other List functions you can use to calculate things like max, min, standard deviations, and other common calculations. This formula now computes the average for that field:
3. Rename your new step. This is an optional step but it is a useful one if you want to reference this value later on. Rather than referencing a ‘Custom’ step, I can right-click on the step and choose to Rename it. Since I’m calculating an average, I’m going to rename it to Average.
Creating a calculated field which compares the percent difference from the average
I’m going to create a calculated column that takes the current price and compares it to the average value that I calculated earlier. Then, I’m going to convert that into a percentage difference. Here are the steps to create a calculated column in Power Query:
1. Add a custom column. Click on the Add Column tab in Power Query and select Custom Column.
2. Enter the formula for the column. You will now be prompted to create your formula and name your new column name. I’ll call this % Difference. And my calculation will take the current value in the Price and divide it by the Average and subtract 1, to get the percentage difference. Here’s what the formula looks like:
One key thing to note here is that I reference the Average as that is the name of the custom step I created in the previous section. It is saved in my steps and so Power Query is referencing that step in this calculation. If you are referencing a previous step, you will want to ensure it is above your calculated step to avoid running into a circular reference.
After clicking on OK, Power Query now creates that additional step for me:
3. Change the format (if necessary). My column has been created but since it is a percentage, I want to change the data type so that it correctly shows a % rather than a decimal number. To do this, I can right-click on the data type (where it shows ABC and 123 underneath), and change that to a percentage. Now my new column correctly displays as a percentage:
For another example of how to run these calculations, refer to the following video:
If you like this post on How to Add a Calculated Column 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.
When you add data to an Excel spreadsheet, with regular charts, you often need to trigger a refresh to make sure that your chart reflects the latest data. But, by using Power Query, you can automate that process without the need for macros. In this post, I’ll walk you through the process of getting your data into Power Query, and how to set it up so that your charts will update automatically.
Step 1: Importing Data into Excel with Power Query
The first step involves pulling data into Power Query. This can be from various sources like databases, web pages, or local files. In this example, I’m just going to use data that’s on another sheet, but it can also be from another workbook. Here is an excerpt of some sales data since the start of the year:
To get this into Power Query, I just need to click on any of the cells in the table and then under the Data tab, under the Get & Transform Data section, select the From Table/Range option. Now my data is in Power Query.
But before loading the data back into Excel, I’m also going to group the totals by week. To do this, I’ll click on the Group By option in the Home tab in Power Query. I’ll create a column name called Weekly Total and sum the Sales Amount:
Now I have sales broken down by week which I can import back into Excel.
At this stage, I’ll click on Close & Load. Now I have another table of the data in Excel, this time, linked to Power Query and broken out by week:
Step 2: Creating a Chart from Imported Data
Next, let’s go ahead and create a chart to show these daily sales totals. For this example, I’ll use a simple column chart showing the weekly sales. To do this, click on your data set anywhere and on the Insert tab, select the option for a Column Chart. After applying some formatting, this is what my chart looks like:
Step 3: Setting Up Automatic Refresh
With my chart and table now setup, I can go ahead and set the automatic refresh. When a query is created in Power Query, you will see it under the Queries & Connections pane. To show this pane, go under the Data tab and click on Queries & Connections. Then, right-click on the query and select Properties. You’ll now see the following options:
As you can see, there is an option to specify how often you want to refresh the data. You can have it refresh when you click on the Refresh All button but you can also set it to refresh when the file first opens. And you can even specify it to refresh every few minutes. I can even set it to refresh every minute:
Depending on how often your data may change, you may want to adjust this accordingly. But one thing to keep in mind is that whatever changes you made in Power Query, refreshing the query will trigger all those steps, which can make it time consuming if there are many steps for the query to go through.
But by setting up a rule to refresh every x number of minutes, you can have control over how often your data updates. And since it’s linked to a chart, your chart will also automatically update.
If you like this post on How to Automatically Update Your Excel Chart With New Data Using Power Query please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
Do you have year-to-date values and need to calculate running totals? What about if you have criteria you want to track running totals for? In this post, I’ll show you how to accomplish both and how you can create a custom function in Power Query that you can re-use in other queries. Here is a simple table I am going to use for this example:
In this table we have date, sales, salesperson, and location. Initially, I am just going to calculate the running total, regardless of the salesperson. I just want to know how much everyone has generated up to a certain point in time. The one step that I need to do is sort the dates in order, so they are going from smallest to largest. You can do this in either Excel or within Power Query.
Creating a running total for everything in Power Query
Step 1: Load your data into Power Query
To load your existing table into Power Query, click on your table and select the Data tab and click on From Table/Range.
Step 2: Sort your data
If your data is not already sorted in Power Query, then select the Date column and click the button to sot in ascending order.
Step 3: Create an Index column
On the Add Column tab, select the option to insert an Index Column, and select to the option to start From 1:
This now creates a simple column that starts from 1.
Step 4: Create a column for the Running Total calculation
Select the Custom Column button from the Add Column tab, which will let you create a column based on a formula.
There are two functions will be used in the formula. The first is the List.Sum function, which as the name describes, will sum up the values in a list. Within that will be the List.FirstN function which will grab all the index values up until that point. The custom column formula to enter, is as follows:
This is how it appears within the Power Query screen:
This now creates a Running Total column which calculates the year-to-date sales values:
Creating a running total for when criteria is met in Power Query
This time around, I’m going to show you how to create a running total which resets for each salesperson. By doing this, you can track their individual year-to-date totals. If you’re starting from scratch, you will need to repeat the steps from the previous section in order to arrive at your Running Total formula.
Step 1: Convert the Running Total formula into custom function
Once you have created the Running Total formula, you can convert it into a custom function. If you select the query and click on the Advanced Editor button on the home tab, you will see the code for all the steps which were previously created:
To reuse this code and use it as a function, I need to add a place to enter a variable at the beginning.
let runningtotal = (x) =>
In the above example, x is the input. It will refer to the table which the custom function will execute on. And at the end of the custom function, there should be another ‘in’ keyword to circle back to the function, such as follows:
in runningtotal
Here is how my custom function looks like after removing the Changed Type step and setting the source equal to the variable:
If you remove any steps, however, just remember to update the formulas. In the “Added Index” step, I updated the step I’m referencing to Source. Otherwise, it would reference a step that doesn’t exist and would result in an error.
After saving, you should notice an fx function next to your query, which you may want to now rename to your desired function name. This is what you will reference it to when invoking the function. In this example, I’m going to call it RunningTotal to keep everything consistent.
Now the function is ready to be used.
Step 2: Group the data
On the Home tab, select the Group By button. This will allow you to break your data into multiple tables.
In the Group By section, select the option to group by Salesperson and use All Rows as the operation, which ensures no data is lost. I’ll name the new column Data.
This now creates a column that contains tables based on the salesperson:
Step 3: Invoke the custom function
On the Add Column tab, press the button to Invoke Custom Function
Then, on the details, enter the column name, the function to invoke, and the data which contains the variable. Since the new ‘Data’ column contains the table, that will be the variable in this example. But if you named the column differently, you would use that name.
This will now create an additional column, which also contains a table.
If you click on any of the cells in the newly created field, you’ll see that the preview contains the running total field:
Step 4: Expanding the table
The last step involves expanding the tables in the newly created field. By pressing on the two arrows going in opposite directions, you will be able to expand all of the columns.
You can omit including the Index column as that is not necessary, and the Salesperson field is already there. The only other column you may want to remove in addition to this is the Data column, but that has to be removed with an additional step. Here is how my final table looks:
You can see that as the name changes to a different salesperson, the running total doesn’t continue adding; it starts from the first date for that salesperson.
If you liked this post on How to Calculate Running Totals in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Have data in Power Query that you want to sum up and group by category? In this post, I’ll show you how you can sum up values in Power Query to help you analyze your information. In this example, I’m going to use daily sales values by different sales reps.
Getting a simple sum
If you just want to calculate a sum in Power Query, select the column you want to sum, and then on the Transform tab, select Statistics, and select Sum.
This will create a new step, where it will calculate the sum.
You could reference this step in other calculations. However, if you want to tally up sales by categories, there’s a better way to do this.
Summing values using Group By
One way to both group and summarize your totals is by using the Group By method. To do this, select the column you want to group by. In this case, it’s going to be by the sales rep, since I want to see the total sales by rep. Then, on the Transform tab, I’ll click on the Group By button:
Then, I’ll enter a column name of Total Sales and for the operation, select Sum and select Sales Amount ($) as the column I want to sum.
After pressing OK, I have a breakdown of sales by the different reps.
There are, however, more splits that you could do. Suppose you wanted to group sales rep sales by the type of product that was sold and the region. To split into more categories, select the Advanced option in the Group By dialog box. From there, you’ll have the ability to specify more levels to break sales down by.
I can click on Add grouping to add more layers, such as Product Category and Region.
Now I have summations based on those different splits.
One thing you may still want to do at this point is to organize the data so that it is in some sort of order. To apply a sort, select the column you want to sort by and on the Home tab, indicate whether you want it to be in ascending or descending order. In the below example, I have arranged the data by sales rep, then product category, and then by region.
If you liked this post on How to Sum and Group Values in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
A common problem you might come across with data is that it may sometimes contain missing values. You can remove the data entirely or you can replace the values with something else. If you remove it, you might throw away other useful data related to that record in the process. If you replace it, you don’t want to just replace missing values with a zero, as that can impact your calculations. The optimal choice may be to replace it with the average of the other values. In this post, I’ll show you how you can easily do that in Power Query.
Calculating an average in Power Query
Here is the data set I’m starting with in Power Query, where you’ll see numerous null values in the Price field:
To calculate an average in Power Query, follow these steps:
Select the column you want to calculate the average for. On the Transform tab, select Statistics and select Average.
Power Query now creates a separate step for me which has calculated the average:
To easily reference this value later on, I’m going to rename this step as AveragePrice.
Replacing the values
Now that you have calculated the average, you can use it to replace the null values in your data set. To do this, you’ll again need to highlight the column which contains null values. Right-click and select Replace Values:
Enter ‘null’ as the value you want to replace and just enter ‘1’ as the value to replace it with. This will just be a temporary placeholder.
In the formula bar, replace the ‘1’ with the name of the step — AveragePrice:
You’ll get an error saying there is a circular reference:
To fix this, drag the new step you just created so that it comes after the AveragePrice calculation step.
This still results in an error, and that’s because the formula is now referencing the AveragePrice step. This needs to be adjusted so that it references the Source step — or the one which contains the data immediately before the average price calculation.
Now the field is correctly updated and the null values have been replaced with the average for the column:
In this situation, we now have eliminated the null values while being able to keep the other fields and simply replaced the empty values with averages.
If you liked this post on how to Replace Missing Values in Power Query With an Average, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Do you want an easy way to pull in crypto prices for multiple coins? Using Power Query in Excel, you can accomplish this by connecting to a website such a coingecko.com and downloading the historical values. Below, I’ll show you how you can pull in historical prices for multiple coins at once, and how you can easily update the values in the future.
Here are the steps to download crypto prices and import them into Power Query from coingecko.com:
Connect to the data source using Power Query.
Download the files into a folder.
Combine and Transform the files in Power Query.
Modify the data to make it consistent.
Update the data.
Step 1: Connect to the data source using Power Query
In this example, I’m using coingecko.com as my source but the process may be similar for other sources. As long as the data as in a table format, there may not be a big difference in the process, however, there could be subtle changes in how you get data from one site versus another.
First, navigate to the coingecko.com website to the cryptocurrency’s price history that you want to download. On the historical data tab, there is a link to download the data:
Step 2: Download the files into a folder
Download this file and save it into a folder. Repeat the process for any other cryptocurrencies that you want to track historical price information for. In this example, I’ve downloaded the price history for Bitcoin, Ethereum, Shiba Inu, and Dogecoin, and saved it within a folder called ‘Crypto’ on my computer:
Step 3: Combine and transform the files in Power Query
Now that all the files have been downloaded, I can use Power Query to consolidate them. With all the files in a folder, I can go and select to get data From Folder:
Then navigate to the folder which contains your downloads:
Since you are selecting a folder, you won’t see the individual Excel files that you have saved — this is fine. Only if you were selecting files would you see the actual files. Once you’ve selected the correct folder, click on the Open button.
Next, select the option to Combine &Transform the files. If you get an error saying that it is an unexpected format, you may need to click on Edit on the next screen. This is because in this example, the data is in a comma-separated value format. After clicking edit, make sure you select the option for a CSV document:
Then, at the next screen, you’ll see that the data has correctly been broken out into columns.
This is how the data looks loaded in Power Query:
Step 4: Modify the data to make it consistent
There are multiple changes that need to be made to the file. The first is to adjust the source.name field so that it reflects the coin and doesn’t include the full filename. To do this, click on the Transform tab and select the option to Extract Text Before Delimiter and use – as the delimiter.
Then, I’ll rename the first two headers to ‘symbol’ and ‘date’
Next, to correctly parse out the date, I’m going to grab the first 10 characters in the field. To do this, select the date column, and on the Transform tab, select Extract and select First Characters:
Specify 10 for the number of characters, and Power Query will remove the rest:
I’ll remove the market_cap and total_volume columns since they aren’t needed. The last step involves pivoting the data so that the crypto symbols are going across. To do that, select the symbol column, and on the Transform tab, click on the Pivot Column button:
Select ‘price’ as the values column. Then, the end result should show the crypto prices going across:
Step 5: Update the data
Now that the data is all entered in Power Query format, the process of updating it at a later date is fairly straightforward. Simply download the Excel files again, save them into the same folder (overwriting the previous files), and then click on the Refresh Data button on the Data tab:
Clicking the button will refresh all the queries and do all the transformations and adjustments that were made earlier. By setting this all up in Power Query, you can easily repeat the process. Just download the latest data, and then click Refresh All.
If you liked this post on Get Crypto Prices for Multiple Coins Into Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Crypto coin dominance is a vital metric in the cryptocurrency market, providing insights into the relative market strength of a particular cryptocurrency, compared to the overall market. It reflects the proportion of a specific cryptocurrency’s market capitalization in relation to the total market cap of all cryptocurrencies. This metric is particularly significant for investors as it indicates the level of risk, market sentiment, and the dominance of major players like Bitcoin and Ethereum.
Formula for calculating crypto coin dominance
To calculate crypto coin dominance, you the formula itself is fairly straightforward: divide the market capitalization of the cryptocurrency in question by the total market capitalization of all cryptocurrencies. Market capitalization, in this context, is calculated by multiplying the current price of the crypto coin by its total circulating supply.
For instance, if Bitcoin has a market cap of $700 billion and the total market cap of all cryptocurrencies is $2 trillion, Bitcoin’s dominance would be 35%.
Why is this useful for investors?
Crypto coin dominance helps investors understand the weight of a particular cryptocurrency in the market, aiding in diversification and risk assessment strategies. A high dominance might suggest a more stable investment but with potentially lower growth prospects, while a lower dominance could indicate a more volatile but possibly high-growth opportunity. Additionally, shifts in dominance can signal broader market trends, helping investors to anticipate and react to market movements.
Calculating crypto coin dominance in Excel is a practical way for investors to actively monitor these shifts and make informed decisions. By regularly updating the market cap data for various cryptocurrencies, investors can use Excel to quickly calculate and track changes in dominance, enabling them to identify trends and adjust their portfolios accordingly.
Pulling in crypto market caps into Excel
To calculate coin dominance in Excel, you can use Power Query. Through Power Query, you can pull in the data, and do calculations to determine the dominance percentage.
You can find a list of the top cryptos by market cap from the following URL in Yahoo! Finance: https://finance.yahoo.com/u/yahoo-finance/watchlists/crypto-top-market-cap/
To get this data into Excel, take the following steps:
Go to the Data tab and select ‘From Web’
Paste the link the following prompt and click OK.
Select the table in Power Query which contains data on the crypto market caps. Then Load the data.
The data is now in Excel but there are no calculations happening just yet. To get this to work, we need the total crypto market cap. The table in Yahoo! Finance didn’t have this information readily available to pull into Power Query. Instead, I’ll leave a place in Excel where the data can easily be entered.
Currently, the crypto market cap is $1.8 trillion. Since the majority of crypto market caps are in billions, I’ll put this in the form of billions, as 1,810.
Next, I’ll load this into Power Query. With one of the cells selected, click on the Data tab again, this time, select From Table/Range
In Power Query, I’m going to rename this table MarketCap and the other one as YahooFinance.
Calculating coin dominance in Power Query
Before I start my calculations, I need to first convert the market caps from the YahooFinance in terms of billions, and remove the ‘B’ that comes after them. Currently, those values are reading as text, and they need to be numbers. In the YahooFinance table, highlight the column which contains the market cap. Then, on the Transform tab, click the Extract option and select Text Before Delimiter
Just enter the letter ‘B’ for the delimiter and press OK on the next screen.
Then, it’s necessary to convert the text into numbers. For that, select the ABC indicator on the header for the market cap, and select DecimalNumber for the type.
After doing so, the data in the column aligns to the right, indicating the field is a number.
The last part involves creating a new column to calculate the market dominance percentage. For this, go onto the Add Column tab in Power Query. Then, select the option for a Custom Column.
I’m going to name the column ‘Dominance’ and this is where I’ll need to enter my formula. The numerator will be market cap column, which in this case is Column9. I will divide this by the value in the MarketCap table. To reference that value, I need to use the following syntax: MarketCap[MarketCap](0}.
The table is called MarketCap, as is the field name, which is in parenthesis. Since I want the first value in the list, I use {0} since it is a 0-based index. That returns the total market cap I entered of 1,810. To put this all together, this is what the formula looks like in my Custom Column calculation:
After clicking OK, now my dominance column appears. Now I can load the data into Excel. All that’s left is to convert the column into a percentage.
Power Query will create another sheet for the MarketCap, but you can delete that as it isn’t necessary.
Updating the file
Moving forward, to update the calculations, all you need to do is update the MarketCap value. This is the only value that won’t pull in automatically from the Yahoo! Finance link. Then, click on the Refresh button on the Data tab and Power Query will pull in the updated market caps for the top cryptocurrencies, and then do the dominance percentage calculation.
Ideally, the total market cap could also be pulled in. However, given the vast number of cryptocurrencies, the list in Yahoo! Finance isn’t comprehensive enough; nor is there a table to pull just from market cap. If you do come across a better source for pulling in crypto market caps, let me know!
If you liked this post on How to Calculate Crypto Coin Dominance Using Power Query in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Excel is a great program for data analysis. And one of the key tools that analysts can use within it is Power Query. While it can seem intimidating for novice users, this guide will walk you through how to use it and how it can help you analyze data.
Getting started with Power Query
Power Query is an Excel tool that enables users to connect, combine, and refine data sources easily. It’s especially useful for automating the data cleaning and preparation process. With Power Query, repetitive tasks like importing data, filtering, sorting, and other transformations become streamlined, saving valuable time.
Power Query was first introduced as an add-in for Excel in 2010 and 2013. However, it became a built-in feature starting from Excel 2016. In these later versions, it’s integrated into the Data tab in the Excel ribbon, offering a more seamless experience compared to the add-in version for earlier Excel releases. Users of Excel 2010 and 2013 can still access Power Query, but they need to download and install the add-in separately. In Microsoft 365 (formerly known as Office 365), Power Query is fully integrated into Excel.
How do I launch Power Query?
To launch Power Query and get started with it, you’ll first want a table or data set in mind that you want to work on. You could link to an external website, workbook, or just have a table or data set within your worksheet that you want to work on. Power Query, after all, is a tool for data analysis — you need data to start with. Here are some of the more common ways to launch Power Query:
1. Connecting to an External Workbook
Navigate to the Data Tab: Go to the Data tab in the Excel ribbon.
Get Data: Click on Get Data in the Get & Transform Data section.
Choose the Source: Select From File and then choose From Workbook.
Select the Workbook: Browse and select the external Excel workbook you want to connect to.
Load or Transform: Once you select the file, you can choose to either load the data directly into Excel or open the Power Query Editor to transform the data before loading.
2. Connecting to a Webpage
Navigate to the Data Tab: Go to the Data tab.
Get Data: Click on “Get Data” in the Get & Transform Data section.
Select Web as Source: Choose From Other Sources and then select From Web.
Enter the URL: Enter the URL of the webpage you want to import data from.
Load or Transform: After connecting to the webpage, you can choose to load the data directly or use the Power Query Editor for transformations.
3. Using a Range or Table Within the Existing Sheet
Create or Select a Range or Table: You don’t need to have your data formatted in a table for Power Query to load it, however, Excel will convert it to a table once you launch Power Query.
Navigate to the Data Tab: Go to the Data tab.
Get Data: Click on Get Data in the Get & Transform Data section.
Choose From Table/Range: Select From Table/Range.
Power Query Editor: This will open the Power Query Editor with the selected table data, ready for transformation.
Each of these methods serves a different purpose. Connecting to an external workbook is useful for consolidating or analyzing data spread across multiple Excel files. Connecting to a webpage allows for the import and analysis of data published online, such as tables on web pages. Using a table within the existing sheet is handy for quickly transforming or analyzing data already present in your workbook. In all cases, Power Query provides a robust set of tools for manipulating the data before loading it back into Excel for further use.
What can Power Query do?
Power Query can help adjust your data before loading it into Excel. Here are some of the key things you can do with it:
Transform data types
Handle missing data
Remove duplicates
Replace values
Filter data
Sort data
Concatenate columns
Split columns
Add conditional columns
Group and aggregate data
Want to follow along with these examples? Download the sample data set I will use here.
Transform data types
One of the first things Power Query does is it attempts to detect your data types. And when it does, it automatically adjusts them, so that numbers are formatted as numbers, and dates as dates. The Changed Type step appears automatically:
If, however, it hasn’t correctly applied a data type, you can make changes to it. You can specify many different data types, including:
Text
Whole Number
Decimal Number
Date
Time
Date/Time
Boolean
To change a data type, click on the header which shows the data type that it is, and then you’ll see a list of different options to choose from:
Handle missing data
Power Query in Excel is equipped with a variety of tools to effectively manage missing or null data, a common issue in data analysis. Ensuring accurate handling of missing data is crucial for the integrity of your analysis. Here are the ways Power Query can assist in managing missing data:
Highlight Null Values: Power Query visually represents null or missing values, making it straightforward to identify gaps in your dataset.
Remove or Keep Rows with Missing Values: You have the option to filter out rows that contain missing values in one or more columns, useful for analyzing complete records. Alternatively, you might want to focus specifically on rows with missing data.
Replace Nulls with Specific Values: Power Query allows for the replacement of missing values with a specified value, such as zero, a specific text string, or an average value. This is beneficial where a missing value has a logical default or substitute.
Fill Down or Up: Filling missing values with the value from the row above or below is possible, which is helpful in datasets where the missing value logically mirrors its neighbor.
Conditional Replacements: Implement logic to replace or handle missing values based on certain conditions, catering to different scenarios for different categories or columns.
Aggregate Functions: When performing functions like sums or averages, Power Query automatically accounts for null values, ensuring they don’t skew the results.
Merging with Missing Data: While merging tables, Power Query can be set up to handle missing values in key columns in various ways, such as including or excluding unmatched rows.
Data Type Impact: Changing data types might create missing values (like when a text can’t be converted to a number). Power Query helps in identifying and handling such cases effectively.
Removing duplicates
Removing duplicate values is an important part of the cleanup process when it comes to data analysis. If, for example, you’re working with a list of transactions or customer records, removing duplicates ensures that each record or transaction is unique and correctly represented in your analysis. And in Power Query, the process to remove duplicates is a fairly straightforward one. It’s as simple as selecting the column and selecting the option to Remove Duplicates.
You can also apply this for multiple columns at once. To select more than one column, just hold down the CTRL key while clicking on other column headers.
Replacing values
Replacing values in Power Query is a useful feature for cleaning and standardizing your data. It allows you to substitute specific values in your dataset with new ones, which is particularly helpful in correcting errors, standardizing terminology, or handling missing data. Think of it like using the Find and Replace feature in Excel. Here’s how it works:
Select the column you want to replace values on. Similar to with removing duplicates, you can select multiple columns.
Select the option to Replace Values.
In the next dialog box, select the value you want to find and what to replace it with.
Filter data
Filtering data in Power Query is a fundamental aspect of data preparation and analysis, allowing you to narrow down your dataset to only the information relevant for your specific needs. The filtering process is similar to how you would do it in Excel. Here’s how it works:
Select the column you want to filter.
Click the drop-down arrow, and you can select how you want to apply your filter:
These are the same type of filters you can apply as in Excel, including criteria such as “contains”, “does not contain”, “starts with”, etc. For numeric columns, you can choose from number filters like “equals”, “greater than”, “less than”, and you can specify ranges. For date columns, you can filter by specific dates, before/after a certain date, or choose from a range of date filters.
After setting your filter criteria, Power Query will display only the rows that meet the criteria. You can apply multiple filters across different columns.
Filtering in Power Query is a versatile tool, allowing for basic operations like removing irrelevant rows, as well as more complex data segmentation, which is essential for detailed and accurate data analysis.
Sorting data
Sorting data in Power Query allows you to organize your dataset in a meaningful order, making it easier to analyze and understand. And by doing it in Power Query, when the data is loaded into Excel, your sorting rules have already been applied. Just like with filtering, the process for sorting data in Power Query is also comparable to how you would do it within your spreadsheet:
Choose the column to sort by.
Select the sort order. For a simple, one-level sort, use the sort ascending (A to Z) or sort descending (Z to A) buttons in the Home tab of the Power Query ribbon. To sort ascending, click the small arrow in the column header and choose “Sort Ascending”. This will organize the data in that column from lowest to highest (e.g., A to Z, 0 to 9, earliest to latest date). To sort descending, click the arrow and choose “Sort Descending”. This will organize the data from highest to lowest (e.g., Z to A, 9 to 0, latest to earliest date).
If you want to sort by multiple columns (for example, first by state, then by city), sort the most significant column first, and then sort by the next column. When you apply multiple sorting rules, you will see a number next to the sorting icon to show you its priority. In the screenshot below, the State field has a 1 next to the up arrow, indicating that the data is first sorted in ascending order by state. Then, it sorts the City field in descending order.
Concatenate columns
Concatenating fields (or columns) in Power Query involves combining the contents of two or more columns into a single column. This process is useful for creating unique identifiers, combining textual information, or formatting data in a more useful way. In this example, we’ll combine the State and City fields, by taking the following steps:
Select the two or more columns you want to concatenate or merge together. The order of your selection is important. If you want the City field first, then that is the one you need to select first.
Right-click and select the option to MergeColumns
Specify if you want them to be separated in any way. In this example, we’ll use a comma so that it is in City,State format.
This results in a field that has grouped the two columns. Those two columns have now been replaced with the new merged column.
Split columns
Splitting columns in Power Query is a handy feature that allows you to divide the contents of one column into multiple columns. This can be particularly useful when dealing with data that’s concatenated into a single column but needs to be separated for analysis or reporting. Here’s how we can undo the previous step, to break out the new City,State column back into separate columns:
Start with selecting the column to split.
Right-click on the option to Split column. Then select how you want it to be split.
These are the ways you can split them:
By Delimiter: Split the column based on a specific character or symbol, such as a comma, space, or custom character. This is the most common method used when data in a column is separated by a consistent symbol.
By Number of Characters: Split the column into new columns each containing a specific number of characters.
By Text Length: Split the column at a specific character position.
Advanced Options: Allow for more complex splits, such as splitting at the first or last occurrence of the delimiter.
In the City,State field, we can split by delimiter — the comma. You can specify how it should be split. But in our example, the default options will suffice:
This will split the columns back into two:
The only thing that may be necessary to do is to rename the columns. To do that, just double-click on the headers and type in a new name for the columns.
Add conditional columns
Adding conditional columns in Power Query is a powerful way to create new columns based on conditions derived from other data in your table. It’s akin to using the IF function in Excel but allows for more complex and multiple conditions. Here’s how you can add a conditional column in Power Query:
Select the Add Column tab on the ribbon.
Select the option to add a Conditional Column.
Next, you can create your conditions, and the column name. With the data set in this example, we can set up a column called Discount Category based on the sales price. This can tell us the type of discount a customer is eligible for. The conditions could be as follows:
If Sales > 3000, then “High Discount”
If Sales is between 1500 and 3000, then “Medium Discount”
Else, “Low Discount”
In the above example, the criteria is evaluated in order from top to bottom. This now creates a new column in the table:
Group and aggregate data
Grouping and aggregating data in Power Query is a crucial process in data analysis, allowing you to summarize and analyze large datasets efficiently. This feature is especially useful for finding averages, sums, counts, minimums, and maximums for different categories or groups in your data. In this example, let’s total the sales by city. Here’s how:
On the Home tab, click on the Group By button
Select City as the initial field. This is how the data will be grouped.
Then, enter a column name (City Sales), an operation (Sum), and specify the column to tabulate (Sales)
This now gives you a summary by city:
You can also do more complex grouping by more than one field. To do so, select the Advanced radio button when in the Group By dialog box. Then, select Add grouping. In this example, we can add the Product_ID field as another field to group by.
Now, when the grouping is completed, it breaks it down further:
Close & Load once you’re done making changes
Once you’ve finished making your changes in Power Query, you can select the option to Close & Load to get it into Excel.
What “Close & Load” Does:
Finalizes Changes: When you click “Close & Load”, Power Query applies all the transformations you’ve made to the dataset within the Power Query Editor.
Loads Data into Excel: After applying the changes, the transformed data is loaded into an Excel worksheet or data model. This can be a new sheet or table, depending on your settings and the nature of your task.
Creates a Connection: Power Query creates a connection between the source data and the Excel workbook. This connection is maintained, which means that you can refresh the data in Excel to reflect any updates in the source data or further transformations applied in Power Query.
Saves Transformations: The sequence of steps or transformations you applied in Power Query is saved. This allows for the data to be updated or reloaded with the same transformations applied automatically.
Why it’s beneficial to make changes in Power Query before loading it into your spreadsheet
You may be wondering why you wouldn’t just make all these changes in your spreadsheet. Why is there a need to make the changes in Power Query? Here’s why:
Data Size Management: Power Query can handle and process large datasets more efficiently than Excel. By filtering, reducing, and transforming data in Power Query, you minimize the load and improve performance in Excel.
Non-Destructive Data Manipulation: Changes made in Power Query don’t alter the original data source. This means you can experiment with and modify your data without the risk of corrupting the original dataset.
Automating Repetitive Tasks: Any sequence of steps you apply in Power Query is repeatable. If you regularly receive data in the same format, you can use the same query to process this data, saving time and effort.
Complex Transformations: Power Query offers more advanced data manipulation capabilities than standard Excel functions, including pivoting/unpivoting, advanced merging and appending, complex filtering, and more.
Data Cleansing and Preparation: It’s often necessary to clean and format data before analysis. Power Query provides a robust set of tools for handling common data issues like missing values, duplicates, and inconsistent formats.
Reduces Workbook Size: By transforming data in Power Query and loading only what’s needed, you reduce the overall size of the Excel workbook, leading to better performance and easier handling.
If you like this post on Power Query for Beginners: A Comprehensive Guide, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
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.
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
3. Enter the value to find and what to replace it with, and then click OK.
Now, Power Query will replace the value for you:
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:
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.
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:
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.
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.
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.
InformationAvailability: Popular stocks generally attract more media coverage, research reports, and analyst attention. This increased coverage provides you with a wealth of information and analysis to make more informed investment decisions. You can leverage these resources to understand the company’s financial health, competitive position, industry trends, and other relevant factors that can impact the stock’s performance.
How to get trending stocks in Excel
To get trending stock data into Excel, you should start with finding a good source that you can rely on for trending data. For this example, I’m going to use apewidsom.io, which provides free access to its API using the following url: https://apewisdom.io/api/. Here’s how I’m going to use that to pull in trending data:
Extract the data using Power Query. To get started, I’ll select the Data tab in Excel and click on the From Web option.
Next, there will be a field to enter the URL, this is where I will paste the link that the API references:
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.
After clicking that, there’s another list of records.
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:
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:
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:
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:
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:
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:
Then, I reference the query as well as the URL variable that is to be used:
Then, there will be a field with the results, in table format. Again, this needs to be expanded out:
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:
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.
