Anzeige

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.

Datenabfragen in Excel mit Power Query

Topic Overview

Anzeige

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.

Datenabfragen in Excel mit Power Query

Topic Overview

Anzeige

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)

PowerQuery - Fiktive VK-Zahlen Abb.1
Advertisement

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)

Excel Datenabfragen Abb.1
Excel Datenabfragen Abb.2

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)

Excel Datenabfragen-Navigator Abb.1
Excel Datenabfragen-Navigator Abb.2

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)

Power Query - Speicherort
Advertisement

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.

  1. You edit the cell formatting afterwards
  2. You transform the data before inserting it into your target file instead of clicking Load or Load into…

see fig. (click to enlarge)

Power Query - Zellformatierung
Power Query - Daten transformieren

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)

Power Query - Arbeitsschritte
Datenabfragen und Verbindungen in Excel

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)

Datenabfragen in Excel aktualisieren
Advertisement

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)

PowerQuery - Fiktive VK-Zahlen Abb.1
Advertisement

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)

Excel Datenabfragen Abb.1
Excel Datenabfragen Abb.2

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)

Excel Datenabfragen-Navigator Abb.1
Excel Datenabfragen-Navigator Abb.2

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)

Power Query - Speicherort
Advertisement

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.

  1. You edit the cell formatting afterwards
  2. You transform the data before inserting it into your target file instead of clicking Load or Load into…

see fig. (click to enlarge)

Power Query - Zellformatierung
Power Query - Daten transformieren

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)

Power Query - Arbeitsschritte
Datenabfragen und Verbindungen in Excel

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)

Datenabfragen in Excel aktualisieren
Advertisement

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 >>>

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

You might also be interested in:

Anzeige

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

You might also be interested in:

Anzeige

Popular Posts:

1907, 2022

Encrypt emails in Outlook – How it’s done

July 19th, 2022|Categories: Data Protection, Homeoffice, Microsoft Outlook, Office 365, Uncategorized|Tags: , , , , , , |

Standard e-mails are like postcards that everyone can theoretically read. But there are solutions for more security.

1707, 2022

Environmentally friendly printing for more climate protection

July 17th, 2022|Categories: Homeoffice, Microsoft Windows, Uncategorized|Tags: , |

Climate protection starts small. And so not only can costs be saved through environmentally friendly and conscious printing in the office, but the climate can also be protected.

Special Offers: Word & Excel Templates

More

Special Offers: Design CV Templates

See more

Monatliche Technik Bestseller:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

Kategorie: SmartHome | Energie & Sicherheit

SmartHome | Energie & Sicherheit

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Produkte anzeigen
Bestseller 2022-2023 Notebooks

Kategorie: Homeoffice

Homeoffice

Bestseller 2022-2023 Notebooks
Produkte anzeigen
Bestseller 2022-2023 Smartphones

Kategorie: Smartphone & Zubehör

Smartphone & Zubehör

Bestseller 2022-2023 Smartphones
Produkte anzeigen

Popular Posts:

1907, 2022

Encrypt emails in Outlook – How it’s done

July 19th, 2022|Categories: Data Protection, Homeoffice, Microsoft Outlook, Office 365, Uncategorized|Tags: , , , , , , |

Standard e-mails are like postcards that everyone can theoretically read. But there are solutions for more security.

1707, 2022

Environmentally friendly printing for more climate protection

July 17th, 2022|Categories: Homeoffice, Microsoft Windows, Uncategorized|Tags: , |

Climate protection starts small. And so not only can costs be saved through environmentally friendly and conscious printing in the office, but the climate can also be protected.

Special Offers: Word & Excel Templates

More

Special Offers: Design CV Templates

See more

Monatliche Technik Bestseller:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

Kategorie: SmartHome | Energie & Sicherheit

SmartHome | Energie & Sicherheit

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Produkte anzeigen
Bestseller 2022-2023 Notebooks

Kategorie: Homeoffice

Homeoffice

Bestseller 2022-2023 Notebooks
Produkte anzeigen
Bestseller 2022-2023 Smartphones

Kategorie: Smartphone & Zubehör

Smartphone & Zubehör

Bestseller 2022-2023 Smartphones
Produkte anzeigen

Title

Anzeige

Most read:

Search by category:

Alle Vorlagen ansehen

Auch interessant:

Go to Top