Power Query

Wat is Power Query?

Met de Power Query wordt er een database functionaliteit in Excel geïntroduceerd. Met een query wordt er gespecifieerd welke gegevens  (de kolommen in een spreadsheet) er moeten worden opgehaald en uit welke bron (intern/extern).

 

In dit artikel wordt ingegaan op het opzetten en onderhouden van een power query. Andere functies met query’s, zoals toe- of samenvoegen wordt in een ander artikel behandeld.

Voordelen van Power Query

Het grootste voordeel van de Power Query is dat er  gegevens kunnen worden samengevoegd uit verschillende werkbladen zonder dat daar bijvoorbeeld programmacode met VBA voor is benodigd. Bovendien hoeven de query’s maar één keer te worden ingericht, waarna ze vast onderdeel uitmaken van het Excelbestand.

 

De query’s kunnen weer geanalyseerd worden met een andere functionaliteit van Excel: Power Pivot. Maar er kan ook gewoon een draaitabel op de output van de query worden gezet voor een quick-and-dirty analyse.

Werking van Power Query

Gegevens (of een bepaalde selectie van gegevens) worden uit een werkblad opgehaald en (eventueel) worden samengevoegd met andere gegevens. Dat kunnen gegevens zijn uit het hetzelfde bestand (andere werkbladen) of externe bronnen.

 

Power Query werkt zowel met een bereik als met tabelobjecten. Excelfactory adviseert om gegevens zoveel mogelijk in tabelobjecten te verwerken. Dit heeft een hoop voordelen. Het volgende artikel  De tabel – de multitool van Excel – Excelfactory beschrijft hoe je een tabel maakt.

De output van een query wordt op een nieuw werkblad altijd als tabelobject toegevoegd aan het Excelbestand waar de query in wordt gemaakt.

 

Wijzigingen worden pas goed overgenomen in de query’s als deze worden uitgevoerd (of vernieuwd). Het vernieuwen van query’s gebeurt door in de menubalk te kiezen voor [Gegevens] en vervolgens [Vernieuwen]. Dit kan een kort moment in beslag nemen. Verifieer voor verder gebruik (d.m.v. steekproef) of de wijzigingen juist zijn overgenomen in de query-bladen.

Nieuw query maken

Er wordt dus een query gemaakt die gegevens uit de tabelobjecten haalt. Dat werkt als volgt:

  • Ga in de tabel staan die moet worden gebruikt voor het maken van de query
  • Selecteer [Gegevens] uit de menubalk
  • Vervolgens [Gegevens ophalen] aan de linkerzijde bovenaan
  • Kies dan voor [Ophalen uit andere bronnen]
  • En dan voor [Tabel/bereik]
  • Er verschijnt een nieuw scherm – de query in bewerkingsmodus – met alle gegevens geladen in een nieuwe lijst

 

Standaard begint de nieuwe naam van de query met [Tabel…]. Door rechts op de naam te klikken kun je de naam van deze query wijzigen.

Onderhoud Power Query

Het kan voorkomen dat er in één of meer invoerwerkbladen kolommen worden toegevoegd, gewijzigd of verwijderd. In dat geval moet de query ook worden gewijzigd.

Wijzigen kolomnaam

Ga naar [Gegevens] in de menubalk en selecteer [Query’s en verbindingen]. Je ziet een lijst met query’s. Klik rechts op de aan te passen query en selecteer [Bewerken]. Er ontvouwt zich een nieuw scherm waar je de query kunt wijzigen.

 

 

Het is de bedoeling dat je de kolom handmatig wijzigt in de code die boven aan de lijst staat. En dat moet op twee punten gebeuren.

  • Selecteer aan de rechterzijde van het scherm [Type gewijzigd]. Bovenaan zie je dan een code zoals: “= Table.TransformColumnTypes(Bron,{{“ABC”, type text}, {“XXX”, typ….”
  • Verander de naam van de kolom in deze code en OK (enter)
  • Er ontstaat mogelijk een fout. Selecteer dan vervolgens [Kolommen verwijderd] eveneens aan de rechterzijde. Er verschijnt een nieuwe code in de trant van: “= Table.SelectColumns(#”Type gewijzigd”,{“XXX”, “XXX”, “XXX….”
  • Verander daar ook de naam en OK (enter)
  • Nu bovenaan links [Sluiten en laden]. Met deze opdracht wordt de query opnieuw uitgevoerd en de gegevens in het nieuwe werkblad geplaatst.

Als er voor de rest geen fouten optreden werkt de query goed.

Toevoegen kolom

Selecteer de query in de modus bewerken. Selecteer bovenaan in het menu [Kolommen kiezen] en geef vervolgens aan welke kolommen je wilt toevoegen aan de query.

Verwijderen kolom

Op dezelfde wijze kunnen ook kolommen verwijderd worden uit de query, door i.p.v. [Kolommen kiezen] voor [Kolommen verwijderen] te kiezen.

Voorbeeld Power Query

In het volgende Excelvoorbeeld is een query toegevoegd die een selectie van financiële gegevens van aan de AEX genoteerde bedrijven ophaalt uit een werkblad [AEX]. Het kan zijn dat het bestand niet wil openen in de browser, kies dan voor het blauwe vakje [Downloaden bestand] bovenaan in de browser.

 

Excelmodel: Power Query AEX

Boekentip

Meer weten over de Power Query? Het volgende boek geeft een uitstekende uitleg.

 

 

Uitgelichte afbeelding bij dit artikel gemaakt door Prawny via Pixabay