Data Queries in Excel – Works with Power Query
Unfortunately, Power Query in Excel is a tool that many users rarely use, although there is a lot that can be done with dynamic data queries in Excel. Presumably it is simply because it still seems too complicated and there are concerns about the reliability of the data. With Power Query you can specifically query data from external Excel workbooks and combine them in a separate Excel workbook and filter and analyze them according to your requirements.
But not only data from Excel tables, but also from entire folders, data sources from the web and much more. is possible with Power Query. In our tutorial, we would like to use a practical example to explain the data query and the merging of data from different Excel spreadsheets using Power Query step by step as simply and comprehensibly as possible.
Data Queries in Excel – Works with Power Query
Unfortunately, Power Query in Excel is a tool that many users rarely use, although there is a lot that can be done with dynamic data queries in Excel. Presumably it is simply because it still seems too complicated and there are concerns about the reliability of the data. With Power Query you can specifically query data from external Excel workbooks and combine them in a separate Excel workbook and filter and analyze them according to your requirements.
But not only data from Excel tables, but also from entire folders, data sources from the web and much more. is possible with Power Query. In our tutorial, we would like to use a practical example to explain the data query and the merging of data from different Excel spreadsheets using Power Query step by step as simply and comprehensibly as possible.
Create data queries in Excel
Create data queries in Excel
In our example, we use two fictitious departments of a large car dealership, which each show their sales figures for the various models separately from January to December. One department only deals with VW models and the other with Audi models. The two departments each enter their sales figures locally in Excel spreadsheets, and we, as the higher authority, would now like to be able to call up and update the sales figures from both departments.
For this purpose, we first opened an Excel file into which the numbers can later be inserted and automatically updated. The two Excel files from which we will retrieve the numbers are not open, and of course we have full access to this data in our local network.
see fig. (click to enlarge)
If you have already searched for the term “Power Query” in your Excel file, I have to disappoint you. Because it doesn’t exist in all versions. We are currently using Office 2021 Professional Plus, and to get to the data queries here, go to:
- “Data” tab
- Call up data
- From file
- From workbook
The first thing you will notice is that there are many more options in Excel than just retrieving data from worksheets or external Excel files. With Power Query you have a tool at hand that can be used to retrieve data from a wide variety of sources and convert it into an Excel-compatible format.
So the first thing we do now is call up the sales figures for the VW models.
see fig. (click to enlarge)
After we have selected our Excel file, a navigator appears in another dialog window in which we are suddenly shown 2 tables, although we have actually only created a single table in this file from which we want the data. This is because Power Query has generated an additional table from the entire workbook and one from the actual table in it from the existing data.
However, we have now at least already created an active data connection to the Excel file with the VW sales figures, and at this point we have the choice of which data, or rather in which form we would like to retrieve and insert the data from this file. If we select table 1, we get the table displayed in the preview of the navigator in which the months are displayed as column titles. And that’s actually how it should be.
If we now select the table VW sales generated by Power Query, we get an additional column title over the months. You can of course choose it that way, but for our example it doesn’t make much sense.
see fig. (click to enlarge)
And now it is like it always is in Excel. Many options lead to the desired result, but they can also be misleading, and then data is suddenly where we don’t want it to be. All things that you then have to edit afterwards. In the Navigator dialog window we could simply click on “Load” and the result would be that the table would be copied from the source file into our file, so to speak. And in an additional spreadsheet.
But since we want to insert the data into our existing worksheet, I recommend going to “Load into” instead, and there we can then specify exactly how and where we want the data to be. So either in an existing spreadsheet at a certain point, as a pivot table, etc.
see fig. (click to enlarge)
After we have determined the position at which the data query should be inserted in our existing spreadsheet and confirmed with OK, the data will be inserted there, but unfortunately not with the correct formatting. All cells are formatted as standard, which then gives us any numbers with decimal places.
This can be fixed in two different ways.
- You edit the cell formatting afterwards
- You transform the data before inserting it into your target file instead of clicking Load or Load into…
see fig. (click to enlarge)
The result is ultimately the same in our small example. However, you still have a few more options to change the data before inserting it using the Transform data step. In your target file, in which the data queries land via Power Query, you also have the option at any time via the “Data” – “Queries and Connections” tab to display the current data connections and to change them again later.
Every work step that you have carried out is also displayed here, and you can also change and/or delete work steps here.
see fig. (click to enlarge)
To set the data connection update options, go to:
- Data Tab
- Queries and Connections
- In the context menu of the existing queries (right mouse button), select Properties
Normally the data is automatically updated when the file is opened, but if, for example, you have your spreadsheet open all day and need up-to-date data without any action on your part, you can also set a time interval for automatic data updates here.
see fig. (click to enlarge)
That’s basically it from the pure functionality. Of course, Power Query in Excel offers a variety of other possibilities. It is important to first understand that Power Query is actually a tool for processing or you could also say converting data from a wide variety of sources so that Excel can work with data that is not actually in Excel format.
In combination with Power Query, a Pivot Table is also suitable for preparing, sorting, merging and organizing data. More about >>>
In our example, we use two fictitious departments of a large car dealership, which each show their sales figures for the various models separately from January to December. One department only deals with VW models and the other with Audi models. The two departments each enter their sales figures locally in Excel spreadsheets, and we, as the higher authority, would now like to be able to call up and update the sales figures from both departments.
For this purpose, we first opened an Excel file into which the numbers can later be inserted and automatically updated. The two Excel files from which we will retrieve the numbers are not open, and of course we have full access to this data in our local network.
see fig. (click to enlarge)
If you have already searched for the term “Power Query” in your Excel file, I have to disappoint you. Because it doesn’t exist in all versions. We are currently using Office 2021 Professional Plus, and to get to the data queries here, go to:
- “Data” tab
- Call up data
- From file
- From workbook
The first thing you will notice is that there are many more options in Excel than just retrieving data from worksheets or external Excel files. With Power Query you have a tool at hand that can be used to retrieve data from a wide variety of sources and convert it into an Excel-compatible format.
So the first thing we do now is call up the sales figures for the VW models.
see fig. (click to enlarge)
After we have selected our Excel file, a navigator appears in another dialog window in which we are suddenly shown 2 tables, although we have actually only created a single table in this file from which we want the data. This is because Power Query has generated an additional table from the entire workbook and one from the actual table in it from the existing data.
However, we have now at least already created an active data connection to the Excel file with the VW sales figures, and at this point we have the choice of which data, or rather in which form we would like to retrieve and insert the data from this file. If we select table 1, we get the table displayed in the preview of the navigator in which the months are displayed as column titles. And that’s actually how it should be.
If we now select the table VW sales generated by Power Query, we get an additional column title over the months. You can of course choose it that way, but for our example it doesn’t make much sense.
see fig. (click to enlarge)
And now it is like it always is in Excel. Many options lead to the desired result, but they can also be misleading, and then data is suddenly where we don’t want it to be. All things that you then have to edit afterwards. In the Navigator dialog window we could simply click on “Load” and the result would be that the table would be copied from the source file into our file, so to speak. And in an additional spreadsheet.
But since we want to insert the data into our existing worksheet, I recommend going to “Load into” instead, and there we can then specify exactly how and where we want the data to be. So either in an existing spreadsheet at a certain point, as a pivot table, etc.
see fig. (click to enlarge)
After we have determined the position at which the data query should be inserted in our existing spreadsheet and confirmed with OK, the data will be inserted there, but unfortunately not with the correct formatting. All cells are formatted as standard, which then gives us any numbers with decimal places.
This can be fixed in two different ways.
- You edit the cell formatting afterwards
- You transform the data before inserting it into your target file instead of clicking Load or Load into…
see fig. (click to enlarge)
The result is ultimately the same in our small example. However, you still have a few more options to change the data before inserting it using the Transform data step. In your target file, in which the data queries land via Power Query, you also have the option at any time via the “Data” – “Queries and Connections” tab to display the current data connections and to change them again later.
Every work step that you have carried out is also displayed here, and you can also change and/or delete work steps here.
see fig. (click to enlarge)
To set the data connection update options, go to:
- Data Tab
- Queries and Connections
- In the context menu of the existing queries (right mouse button), select Properties
Normally the data is automatically updated when the file is opened, but if, for example, you have your spreadsheet open all day and need up-to-date data without any action on your part, you can also set a time interval for automatic data updates here.
see fig. (click to enlarge)
That’s basically it from the pure functionality. Of course, Power Query in Excel offers a variety of other possibilities. It is important to first understand that Power Query is actually a tool for processing or you could also say converting data from a wide variety of sources so that Excel can work with data that is not actually in Excel format.
In combination with Power Query, a Pivot Table is also suitable for preparing, sorting, merging and organizing data. More about >>>
About the Author:
About the Author:
Popular Posts:
With the webcam to your dream job: Preparation for the video job interview!
Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.
IT security: The deceptive appearance of digital security
In a digitized world, IT security is an important factor. But technology alone is not enough. A holistic strategy is needed that also takes into account the human component.
Import Stock Quotes into Excel – Tutorial
Importing stock quotes into Excel is not that difficult. And you can do a lot with it. We show you how to do it directly without Office 365.
Create Excel Budget Book – with Statistics – Tutorial
Create your own Excel budget book with a graphical dashboard, statistics, trends and data cut-off. A lot is possible with pivot tables and pivot charts.
Excel random number generator – With Analysis function
You can create random numbers in Excel using a function. But there are more possibilities with the analysis function in Excel.
Excel Database with Input Form and Search Function
So erstellen Sie eine Datenbank mit Eingabemaske und Suchfunktion OHNE VBA KENNTNISSE in Excel ganz einfach. Durch eine gut versteckte Funktion in Excel geht es recht einfach.
Spring Specials 2024: Word & Excel Templates
Popular Posts:
With the webcam to your dream job: Preparation for the video job interview!
Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.
IT security: The deceptive appearance of digital security
In a digitized world, IT security is an important factor. But technology alone is not enough. A holistic strategy is needed that also takes into account the human component.
Import Stock Quotes into Excel – Tutorial
Importing stock quotes into Excel is not that difficult. And you can do a lot with it. We show you how to do it directly without Office 365.
Create Excel Budget Book – with Statistics – Tutorial
Create your own Excel budget book with a graphical dashboard, statistics, trends and data cut-off. A lot is possible with pivot tables and pivot charts.
Excel random number generator – With Analysis function
You can create random numbers in Excel using a function. But there are more possibilities with the analysis function in Excel.
Excel Database with Input Form and Search Function
So erstellen Sie eine Datenbank mit Eingabemaske und Suchfunktion OHNE VBA KENNTNISSE in Excel ganz einfach. Durch eine gut versteckte Funktion in Excel geht es recht einfach.
Spring Specials 2024: Word & Excel Templates
