Power Query Tip to Ignore Subfolders
Using the From Folder option in Power Query allows you to import all the files from the selected folder and all its subfolders. What if you didn’t want the subfolders? There is a solution.
(more…)Excel Two Column Lookup – Part 2
In the previous post I showed how to use XLOOKUP to do a two-column lookup. See the link below for the previous post. This time we will see how to do the two column lookup using Power Query.
(more…)Power Query Merge
Merging in Power Query is a useful feature that automates and replaces XLOOKUP functions. But please make sure the look up table doesn’t have duplicates.
(more…)Handling Extra Columns in Excel Power Query
When you import a CSV file using Power Query any extra rows are automatically included. Unfortunately, extra columns are NOT included. Here’s how to fix that. In the image below I have the Formula Bar showing for the Source step for importing a CSV file. I have highlighted the argument that specifies how many columns it will import. This argument is optional. You can edit in the Formula Bar and delete that argument. Then when you refresh the Power Query…
Sharing a Power Query Between Files
If you have a created a power query to import data you can save it and have another file access it easily.
(more…)Extracting Data from the Web with Power Query
Power Query can import table-based data direct from the web. As an example, we can extract the public holiday dates for Western Australia from the WA government website. This process is sometimes called web scraping.
(more…)Introduction to Power Query 2025
In January 2025 I ran a free webinar on Power Query - the best way to import data into Excel. 170 attended and over 100 people provided feedback with an average score of 92%. Some of the feedback is shown below. Introduction to Power Query10/10Really enjoyed this webinar - the conversion of MYOB style reports is such a useful tool to know.C StevensonJanuary 2025 Introduction to Power Query10/10Terrific session - very clear and concise and very useful information. The explanation…
Excel Power Query Errors List
Sometimes Power Queries generate errors. Some errors stop all data being returned and others will return a blank cell for the error in the returned table. You can list the rows that are generating the errors. This can help you identify what is causing the error.
(more…)Excel Power Query and Multiple Files 2023
In this recording of a live webinar I ran in late January 2023 you will learn how to import multiple files in a single Power Query. If you use data at all then Power Query is an essential skill to possess. Use the buttons below the video to download the materials. Download Materials Building on the skills covered in the Introduction session, we will start working with multiple files. For example you may have 12 separate CSV files in a…
Introduction to Excel Power Query 2023
In this recording of a live session ran in late January 2023 you will learn how to automate data importation in Excel with Power Query. If you use data at all then Power Query is an essential skill to possess. Use the buttons below the video to download the materials. Download Materials Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use.…
Excel Power Query Saving Issue
There was a recent discussion of this topic on LinkedIn. When you are in the Power Query window you can’t save the query. If you Close and Load and Excel crashes, you could lose your work. If you have spent some time working on a query this can be frustrating. Here’s how you can capture the query before using Close & Load.
(more…)Excel Power Query and Multiple Files 2022
This is the recording of the second free Power Query webinar I ran in 2022. You can watch the first one at this link. In this session we see how to import multiple files in one Power Query. We look at importing CSV and Excel files. You can download the materials, including a detailed pdf manual using the button below. Download Materials https://vimeo.com/698458526
Handling Multiple Columns in Power Query
In general, you should reduce the number of columns you import via Power Query to the minimum you require. Here is a quick technique to make that a bit easier.
(more…)Excel Power Query and Data Types
Data types are an important part of Power Query in Excel and Power BI. They define the type of data that should be in a column. When performing some calculations, getting the column data type right is vital.
(more…)Convert text time to real time in Excel
I recently downloaded an example file for an Excel challenge. The challenge had a lot of things to do but they were all based on a Timestamp column that had text instead of times.
(more…)Power Query Finding Text in Another Column
If you need to find if text is in a column you can use the Text.PositionOf function to confirm it exists.
(more…)Financial Year in Power Query
If you need to extract the Australian Financial Year from a date in Power Query here is how to do it.
(more…)Segmenting a Table with Power Query
In my previous post I mentioned you should, as far as possible, keep data together in a single table rather than splitting it up between sheets. If you want to split it up for distribution purposes here is an easy way to do it.
(more…)Don’t split your data in Excel
I have received a few questions recently relating to working with data spread across multiple sheets. In general, if the data is in the same layout, keep it in one table.
(more…)Stop Power Query from automatically setting Data Types
When you transform data using Power Query it will often automatically promote the headers (use first rows headers) and guess the data types for each column. You can stop it doing this.
(more…)Power Query and large data sets
When working with some large data sets in Power Query it may be useful to default your Close and Load option to Connection Only rather than a Table. That way if you do strike an issue it won’t take ages to load to data.
(more…)IFERROR in Power QUERY
Power Query does not have an IFERROR function. It does however have another function that performs the same type of calculation.
(more…)Creating a list of files in Excel
Let’s say that you need to create a list of files in a folder. This is possible using VBA, but it is also possible in Power Query and it’s much easier to do in Power Query.
(more…)Creating Groups for Power Queries in Excel
Once you start to use Power Query you may find yourself with quite a few queries in the one file. To make it easier to control them you can use groups to keep similar queries together. (more…)
Free Webinar Recording – Introduction to Power Query
In October 2019 I ran my Introduction to Power Query webinar for free (previously it was a paid session). I want to get this information out to as many people as possible. please share this resource with colleagues and your network. The detailed pdf manual and example file can be downloaded by using the button below. Content listed below the video. Download Materials Power Query allows you to automatically perform data cleansing routines on your data sources – no manual…
Promoting Headers Issue in Power Query
Promoting headers in Power Query means using the first row as column headers. In Power Query this is a useful and common option. In some cases it is even automated. There is one time though when you don’t want to use it. (more…)
Power Query Fill Down Doesn’t Work
Sometimes when working with CSV files in Power Query you may strike the situation where Fill Down doesn’t fill down. Don’t worry there is an easy fix. (more…)
Power Query and Open Excel Files
Power Query can easily combine data from multiple Excel files. A problem can arise if one or more of the files is open. Power Query will generate an error and the import will fail. The solution involves an old school Excel feature. (more…)
![Read more about the article Creating a table of all combinations in Excel [Video]](https://a4accounting.com.au/wp-content/uploads/2018/01/vimeo-250571093.jpg)
Creating a table of all combinations in Excel [Video]
Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table. (more…)
Creating a Unique Dynamic List in Excel
There are a couple of techniques to automate a unique list of items in Excel. I have covered them in previous blog posts (see links below). I thought I would describe how to use Power Query to create a dynamic unique list. (more…)