Excel und Power BI mit dem Querysystem verbinden

Übersicht

Sie können Querydaten direkt in Excel laden und per Knopfdruck aktualisieren. Statt eines Tokens in der URL verwenden Sie ein persönliches Zugriffstoken (Personal Access Token). Excel speichert das Token sicher ausserhalb der Arbeitsmappe, nicht im Datei selbst.

Vorteile gegenüber dem alten Link

• Das Token liegt nicht in der URL und damit nicht in Logs, Browser-Verlauf oder der Excel-Datei.
• Aktualisieren funktioniert über den Aktualisieren-Knopf in Excel, ohne die URL von Hand zu bearbeiten.
• Das Token ist langlebig, an Ihre Person gebunden und jederzeit widerrufbar.

Schritt 1: Zugriffstoken erstellen

1. Öffnen Sie in SwissMentor die Einstellungen und wählen Sie Excel-Zugriff.
2. Geben Sie einen Namen ein (zum Beispiel «Excel Auswertung») und klicken Sie auf Token erstellen.
3. Kopieren Sie das angezeigte Token sofort. Es wird aus Sicherheitsgründen nur einmal angezeigt.

Verlorene Token können Sie nicht erneut anzeigen. Erstellen Sie in diesem Fall ein neues Token und widerrufen Sie das alte.

Schritt 2: Daten in Excel laden

1. In Excel: Daten → Daten abrufen → Aus dem Web (oder Von Web).
2. Geben Sie die URL des Querysystems ein, zum Beispiel https://<ihre-instanz>/query/<slug>.
3. Bei der Authentifizierung wählen Sie Basic.
4. Tragen Sie als Benutzername Ihren SwissMentor-Benutzernamen ein und fügen Sie das Token als Passwort ein.
5. Bestätigen Sie. Excel lädt die Daten und merkt sich die Anmeldedaten im Windows-Anmeldeinformations-Manager.

Schritt 3: Parameter übergeben

Parameter hängen Sie als Query-String an die URL an, zum Beispiel:

https://<ihre-instanz>/query/<slug>?jahr=2026&klasse=A

Mehrfachwerte für denselben Parameter geben Sie wiederholt an: ?status=offen&status=bezahlt.

Aktualisieren

Über Daten → Alle aktualisieren lädt Excel die Daten neu. Das Token bleibt gespeichert; die URL muss nicht angepasst werden.

Token widerrufen

Unter Einstellungen → Excel-Zugriff sehen Sie Ihre aktiven Token und können sie einzeln widerrufen. Ein widerrufenes Token funktioniert sofort nicht mehr.

Variante: OData-Feed (empfohlen für grosse Abfragen)

Statt „Aus dem Web” können Sie eine Query auch als OData-Feed laden. Das ist die bessere Wahl für grosse oder lang rechnende Abfragen, weil Excel die Daten seitenweise nachlädt.

1. In Excel: Daten → Daten abrufen → Aus anderen Quellen → Aus OData-Feed.
2. URL: https://<ihre-instanz>/odata/<slug> (Parameter wie gewohnt anhängen, z.B. ?jahr=2026).
3. Authentifizierung Basic: SwissMentor-Benutzername + Token.

Hinweise zum OData-Feed:

• Die erste Aktualisierung kann bei grossen Abfragen einige Minuten dauern (die Daten werden berechnet und zwischengespeichert). Danach lädt Excel die weiteren Seiten und spätere Aktualisierungen schnell aus dem Cache.
• Excel blättert automatisch durch alle Seiten (@odata.nextLink); Sie erhalten immer den vollständigen Datensatz.
• Filtern erfolgt über die Parameter der Query, nicht über $filter.

Abfragen mit Parametern (Excel fragt nach)

Hat eine Query Parameter, erscheint sie im OData-Navigator als Funktion (fx-Symbol) statt als Tabelle:

1. In Excel: Aus OData-Feed mit der Service-URL https://<ihre-instanz>/odata verbinden.
2. Im Navigator die gewünschte Query (Funktion) wählen.
3. Excel fragt die Parameter ab → Werte eingeben → Aufrufen → Laden.

Beim Aktualisieren verwendet Excel die zuletzt eingegebenen Werte. Pflichtparameter müssen gesetzt sein; Paging (oben/überspringen) wird automatisch behandelt.

Power BI Desktop

Das persönliche Zugriffstoken funktioniert auch mit dem Web- und OData-Zugang in Power BI Desktop.

1. Erstellen Sie unter Einstellungen → Excel-Zugriff ein eigenes Token mit einem erkennbaren Namen, zum Beispiel «Power BI Monatsbericht».
2. Wählen Sie in Power BI Desktop Daten abrufen → OData-Feed.
3. Geben Sie die Service-URL https://<ihre-instanz>/odata ein.
4. Wählen Sie Basic und hinterlegen Sie Ihren SwissMentor-Benutzernamen sowie das Token als Passwort. Wenden Sie die Anmeldedaten auf die Adresse /odata an.
5. Wählen Sie im Navigator die gewünschte Abfrage, setzen Sie gegebenenfalls deren Parameter und laden Sie die Daten.

Bestehende JSON-Abfragen können weiterhin über Web und https://<ihre-instanz>/query/<slug> geladen werden. Hinterlegen Sie die Zugangsdaten im Anmeldedialog. Token gehören weder in die URL noch in den Power-Query-Code. Microsoft beschreibt die Einrichtung des OData-Connectors.

Veröffentlichte Berichte automatisch aktualisieren

Die Aktualisierung im Power BI Service benötigt eine eigene eingerichtete Datenverbindung. Prüfen Sie nach dem Veröffentlichen in den Einstellungen des semantischen Modells die Gateway- und Cloudverbindungen, die Datenquellen-Anmeldeinformationen sowie den Aktualisierungszeitplan. Microsoft führt für den OData-Connector bei Basic eine Gateway-Anforderung auf. Ordnen Sie die passende Gateway-Datenquelle mit SwissMentor-Benutzername und Token zu.

Testen Sie zuerst eine Aktualisierung im Service und richten Sie danach den Zeitplan ein. Ein erfolgreicher Abruf in Desktop bestätigt noch nicht die Einrichtung im Service. Bei Ablauf oder Wechsel eines Tokens müssen Sie die hinterlegten Zugangsdaten in Desktop und Service/Gateway aktualisieren. Prüfen Sie den Abruf mit dem neuen Token, bevor Sie das alte widerrufen. Siehe Microsoft: Geplante Aktualisierung konfigurieren.

Hinweise

• Der Zugriff erfolgt ausschliesslich über HTTPS. Übermitteln Sie das Token nie über HTTP.
• Das Token gewährt nur die Berechtigung für das Querysystem. Der Zugriff auf einzelne Abfragen bleibt durch die normalen Berechtigungen eingeschränkt.
• Der alte Link mit Token in der URL funktioniert weiterhin, gilt aber als veraltet und sollte nicht mehr verwendet werden.