Load data into Excel with Power Query

In this tutorial, you load a table from the data service into Excel, filtered by the service. Power Query reads the OData interface directly; you don't need an ODBC driver for it. You sign in with your personal key, which Excel keeps in its own credential store, not in the workbook.

• Excel with Power Query (Microsoft 365 or Excel 2016 and later)
• the service address as in step 4 of the set-up guide, here https://daten.example.com, with a certificate the computer trusts
• the name of the data source (alias) and the schema, here fachdaten and dbo; your Gateway administrator gives you both
• a personal key with read access. If the sign-in policy requires a second factor for you, you need a key without a second factor, because Excel can't ask for a code; see Notes

In Excel, select Data > Get Data > From Other Sources > From OData Feed.

Under URL, enter the address of the data source with its schema and select OK:

https://daten.example.com/odata/v4/fachdaten/dbo/

Excel asks how to sign in to the address.

1. On the left, select Basic.
2. Enter pkuser as the User name and your personal key (wkl_…) as the Password.
3. Under Select which level to apply these settings to, choose the service address without a path (https://daten.example.com/), so the sign-in applies to every data source of this service.
4. Select Connect.

The user name pkuser is a convention: the service recognizes the key by its form, not by the name; see HTTP: sign-in.

The Navigator shows the tables and views you may read. Select a table, such as Artikel, and then Transform Data. In the Power Query editor, open the menu of the Bezeichnung column and select Text Filters > Contains… with the value Keller. On the right, under Query Settings, enter Keller as the Name; the worksheet gets the same name.

If you want the whole table, select Load in the navigator right away and skip step 5.

Select Home > Close & Load. Excel creates a worksheet with the result.

The sheet contains only the rows whose Bezeichnung contains “Keller”. Power Query sent the condition to the service as $filter=contains(Bezeichnung,'Keller'), so the database did the filtering, not Excel. Refresh reads the current data.

Power Query shows column names with special characters (F&E) as they appear in the database; table names with special characters appear encoded in the navigator (_x0026_).

• Second factor: Excel can't ask for a second-factor code. If the sign-in policy requires a second factor for you, sign-in fails. Your Gateway administrator then issues you a key with the purpose Arbeitsplatz (Workstation) and selects Nur lesen (Read only) and Ohne zweiten Faktor (No second factor) in its form: “Create a person and issue a key” (documentation of the administration app).
• Change or remove the key: Select Data > Get Data > Data Source Settings, choose the service address, and select Edit Permissions… or Clear Permissions.
• Without a key: On the company network, Excel can also sign in with the Windows account (authentication kind Windows) if the service is set up for it; see Windows sign-in.
• Sharing: The workbook contains the address but not the key. Whoever opens and refreshes it signs in with their own key and sees only what they may read.

• Set-up guide: from an empty server to data in Access, Excel, and Power BI
• Load data into Power BI Desktop—the same way in Power BI
• HTTP: OData v4 (reference)—what Power Query passes on to the service
• Tier access by location—a report key at a customer's site