Met Ophalen en transformeren in Excel 2016 kunt u zoeken naar gegevensbronnen, hiermee verbinding maken en vervolgens de gegevens structureren (bijvoorbeeld een kolom verwijderen, een gegevenstype wijzigen of tabellen samenvoegen) op manieren die aansluiten bij uw behoeften. Wanneer u de gegevens hebt gestructureerd, kunt u de resultaten delen of de query gebruiken om rapporten te maken. 

Deze stappen vinden vaak plaats in de volgende volgorde:

• Verbinding maken: maak verbinding met gegevens in de cloud of een service of lokale gegevens
• Transformeren: structureer de gegevens zo dat deze aansluiten op uw behoeften. De oorspronkelijke bron blijft ongewijzigd
• Combineren: maak een gegevensmodel van meerdere gegevensbronnen en krijg een uniek inzicht in de gegevens
• Delen: wanneer de query is voltooid, kunt u deze opslaan, delen of gebruiken voor rapporten

Telkens wanneer u verbinding maakt met gegevens, deze transformeert of combineert met andere gegevensbronnen, wordt elke stap vastgelegd met een functie van Ophalen en transformeren, de Queryeditor. Hier kunt u elke stap wijzigen zoals u maar wilt. In de Queryeditor kunt u ook handelingen ongedaan maken, opnieuw uitvoeren, de volgorde wijzigen of een stap wijzigen. U kunt uw weergave van de verbonden gegevens dus geheel naar wens vormen.

Met Ophalen en transformeren kunt u query’s maken die zo eenvoudig of complex zijn als nodig is. Naarmate u stappen aan een query toevoegt, werkt Queryeditor achter de schermen om een reeks afzonderlijke instructies te maken die uw opdrachten uitvoeren. Deze instructies worden gemaakt in de taal M. Gebruikers die de kracht en flexibiliteit van gegevensscripts kennen, kunnen M-taal maken om query’s handmatig te maken (of aan te passen) met de Geavanceerde editor. Alle mogelijkheden en flexibiliteit van zowel het automatisch als handmatig maken van stappen, vindt u in de reeks functies voor Ophalen en transformeren van Excel 2016. Queryeditor en de Geavanceerde Editor worden verderop in dit artikel gedetailleerder beschreven.

U begint een nieuwe query door de knop Nieuwe query op het lint Ophalen en transformeren in Excel 2016 te kiezen.

Verbinding maken

U kunt een query gebruiken om verbinding te maken met één gegevensbron, zoals een Access-database, of met meerdere bestanden, databases, OData-feeds of websites die zijn verspreid over internet. Met Ophalen en transformeren kunt u al deze bronnen samenvoegen volgens uw eigen unieke combinaties, en tot geheel nieuwe inzichten komen.

Wanneer u Nieuwe query selecteert in de lintsectie Ophalen en transformeren van het tabblad Gegevens, worden de beschikbare gegevensbronnen weergegeven in een menu. U kunt uit vele verschillende gegevensbronnen kiezen, waaronder bestanden zoals Excel-werkmappen of CSV-bestanden, databases zoals Access, SQL Server, Oracle en MySQL, Azure-services zoals HDInsight of Blob Storage, en allerlei andere bronnen zoals het web, SharePoint-lijsten, Hadoop-bestanden, Facebook, Salesforce, en vele andere.

Wanneer u verbinding maakt met een gegevensbron, wordt met Ophalen en transformeren een Navigator-venster geopend waarin u de gegevens vanuit de bron kunt bewerken. Wanneer u in het venster NavigatorBewerken kiest, wordt via Ophalen en transformeren de Queryeditor gestart, een speciaal venster waarin u eenvoudig uw gegevensverbindingen kunt instellen en bekijken, samen met de transformaties die u hebt toegepast. De volgende sectie, Transformeren, bevat meer informatie over Queryeditor.

Transformeren

Met Ophalen en transformeren kunt u de gegevens van uw gegevensbronnen transformeren, zodat u deze beter kunt analyseren. Het transformeren van gegevens houdt in dat u de gegevens enigszins aanpast aan uw behoeften. U kunt bijvoorbeeld een kolom verwijderen, een gegevenstype wijzigen of tabellen samenvoegen: dit zijn allemaal gegevenstransformaties. Wanneer u gegevens transformeert, worden deze in de gewenste vorm gegoten, zodat u ze beter kunt analyseren. Het proces van het toepassen van transformaties op een of meer gegevenssets wordt vaak data shaping genoemd.

In Excel wordt een speciaal venster, de Queryeditor gebruikt om gegevenstransformaties te vergemakkelijken en weer te geven. Wanneer u Nieuwe query in de lintsectie Ophalen en transformerenvan het tabblad Gegevens selecteert en daarna de gegevensbron (bijvoorbeeld een werkmap of database) selecteert, wordt een Navigator-venster geopend waarin u kunt selecteren welke tabel(len) u in uw query wilt gebruiken. Wanneer u een tabel selecteert, wordt in het rechterpaneel van het venster Navigator een voorbeeld van de gegevens weergegeven.

Als u Laden selecteert, wordt de gegevensbron rechtstreeks overgebracht naar Excel. In dit geval selecteren we Bewerken om Queryeditor te starten.

In Queryeditor worden alle gegevensbewerkingen bijgehouden. In Queryeditor wordt elke transformatie of stap die u toepast op de gegevens, vastgelegd en gelabeld. Ongeacht of het bij de transformatie gaat om het maken van verbinding (een gegevensbron), het verwijderen van een kolom, een samenvoeging of het wijzigen van een gegevenstype, in Queryeditor wordt elke bewerking bijgehouden in de sectie TOEGEPASTE STAPPEN van het deelvenster Queryinstellingen.

De transformaties die u op uw gegevensverbindingen toepast, vormen gezamenlijk de query.

Het is belangrijk (en handig) om te weten dat de oorspronkelijke brongegevens niet worden gewijzigd door de acties die u in Queryeditor definieert. In plaats daarvan wordt in Excel elke stap vastgelegd die u uitvoert bij het maken van verbinding of het transformeren van gegevens. Wanneer u klaar ben met het structureren van de gegevens, wordt er een momentopname gemaakt van de verfijnde gegevensset en in de werkmap gezet.

Er zijn heel veel transformaties die u op gegevens kunt toepassen. U kunt ook uw eigen transformaties schrijven met de M-taal (waarmee in Queryeditor op de achtergrond stappen worden vastgelegd) in de Geavanceerde editor van Queryeditor. U opent de Geavanceerde editor vanaf het lint Beeld van Queryeditor. Daar kunt u de stappen in de M-taal wijzigen die zijn gekoppeld aan de bestaande query. U kunt ook nieuwe query’s maken vanuit de Geavanceerde editor.

Wanneer u klaar bent met het maken van uw query, kunt u Sluiten en laden selecteren op het linttabblad Start. De queryresultaten worden dan in Excel geladen en weergegeven op een nieuwe tab in een werkmap.

Delen

Wanneer u een Excel-werkmap opslaat die een query bevat, wordt de query ook automatisch opgeslagen. U kunt alle query’s in een Excel-werkmap bekijken door Queries tonen te selecteren in de lintsectie Ophalen en transformeren van het tabblad Gegevens.

Het deelvenster Werkmapquery’s bevat alle query’s in de werkmap.

Maar dat is niet alles. Met de Power BI-gegevenscatalogus kunt u uw query’s delen met iedereen in uw organisatie. U kunt ook een query maken die u vaak gebruikt, deze in meerdere werkmappen gebruiken en uzelf een hoop werk besparen. In plaats van Excel‐werkmappen op te slaan en via e‐mail te verzenden ﴾en te proberen bij te houden welke versie de oorspronkelijke versie is, wat er is gewijzigd en of de gegevens mogelijk verouderd zijn﴿, slaat u een query op in de gegevenscatalogus, zodat u niet meer zit opgescheept met talloze niet‐bijgehouden werkmapversies. Klik met de rechtermuisknop op een query in het deelvenster Werkmapquery’s om een menu met diverse opties weer te geven, waaronder Verzenden naar gegevenscatalogus.

Kijk ook naar de andere opties in het snelmenu. U kunt een query dupliceren, zodat u bepaalde elementen (of alle elementen) van een query kunt wijzigen zonder de oorspronkelijke query te wijzigen. Dit is hetzelfde idee als het maken van een querysjabloon die u vervolgens kunt wijzigen om aangepaste gegevenssets te maken, zoals een gegevensset voor de detailhandel, een andere set voor de groothandel en nog een voor de voorraad, waarbij alle gegevenssets zijn gebaseerd op dezelfde gegevensverbindingen.

U kunt query’s ook Samenvoegen of Toevoegen om deze te gebruiken als herbruikbare bouwstenen.

U kunt een werkmap ook publiceren naar Power BI en onlinerapporten maken die automatisch kunnen worden gedeeld met uw groep, automatisch vernieuwd en verfijnd. Selecteer Bestand > Publiceren > Publiceren naar Power BI als u een werkmap wilt publiceren naar Power BI.

Wanneer de query klaar is, kunt u hiermee rapporten maken in Excel of Power BI. Bekijk de volgende sectie voor meer informatie over rapportbronnen waarmee u optimaal gebruik kunt maken van de gegevens die u met Ophalen en transformeren hebt gestructureerd op basis van uw behoeften.