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…)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.…
Open Power Query Shortcut
Woohoo ! A new shortcut to open the Power Query window. Works in the latest subscription version of Excel. Alt + F12
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
Introduction to Excel Power Query
Earlier this month I ran a free webinar on Excel Power Query. This is the recording of the session with no editing, no time limit and no sign up required. Power Query is the best way to import data into Excel. It is also the data importation system used in Power BI, so everything you learn in Excel can be applied to Power BI. You can download the materials, including a detailed pdf manual at the button below. Please enjoy,…
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…)Power Query Zoom
I used this today in a live webinar. I zoomed into the Power Query window in Excel to make it easier to see. Ctrl + Shift + + (plus)
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…)![Read more about the article Excel Power Query and Multiple Files [one hour video]](https://a4accounting.com.au/wp-content/uploads/2021/05/vimeo-548711986.jpg)
Excel Power Query and Multiple Files [one hour video]
Getting data into Excel and in the correct layout and format is the important starting point for any Excel project. Power Query can automate the importation process. It is now a vital skill to possess. This is a follow up session to the Introduction session which you can view here. This session focuses on importing multiple files from a folder. The session uses CSV and Excel files plus Excel tables. This is a video of a free live training session…
Introduction To Excel Power Query [one hour video]
Getting data into Excel and in the correct layout and format is the important starting point for any Excel project. Power Query can automate the importation process. It is now a vital skill to possess. This is a video of a free live training session I ran in May 2021 - over 100 people attended - please share this resource with friends and colleagues. No editing has been done to the video. Use the button below to download the pdf…
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…)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…)Power Query shortcut for Adults
The shortcut to display the Queries & Connections Task Pane is easy to remember. It is Adults Only! To display or hide the Queries & Connections Task Pane you use. These keys are pressed in sequence, not held down. So only adults allowed in Power Query.
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…)Grouping Measures in Power BI
It is considered best practice to use Measures for all your values in Power BI reports and dashboards. One way to make it easier to find Measures is to group them together. (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…)
Changing the Close & Load settings in a Power Query
A recent attendee at a webinar posed the question, can you change the Close & Load setting on an existing query? Here is the answer. (more…)
Power Query feedback
Received this email after a recent Power Query session - some things may take a few goes to understand, that's OK, that's how we learn.
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
Its always great to receive e-mails like this. Power Query is a game changer.