In het vorige artikel had ik aangegeven dat ik zou beschrijven hoe je gegevens die over meerdere internet-pagina’s zijn verspreid toch met Power Query in één keer kunt overhalen naar Excel.
Belofte maakt schuld ….
Basis
Allereerst een disclaimer: je kunt (natuurlijk) alleen maar gegevens van meerdere pagina’s combineren, wanneer die pagina’s qua structuur op elkaar lijken.
We nemen als voorbeeld de pagina Tips & trucs van G-Info.
Daar staan alle artikelen die in de loop van de jaren zijn gepubliceerd; tenminste het eerste gedeelte van die artikelen.
Om het overzichtelijk te houden vind je daar de artikelen in blokken van 5.
Kies je oudere artikelen dan zie je in de adresbalk van je browser: www.ginfo.nl/tips-trucs/page/2/, waarbij het paginanummer gaat oplopen.
NB gelukkig kunnen we ook op dezelfde manier de eerste pagina ophalen: www.ginfo.nl/tips-trucs/page/1/
We gaan de gegevens van de eerste pagina binnenhalen in Excel:
- Analyseer de internetpagina via de optie Inspecteren zoals in het vorige artikel uitgelegd.
- Kies in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web en geef als URL in: ginfo.nl/tips-trucs/
- Klik in de Navigator op Document en kies Bewerken.
- Klik 2x op Table en dan op de Table achter Body.
- Dan 4x op Table achter de (eerste) DIV
Nu we op het niveau van de artikelen zijn gekomen kiezen we Uitvouwen achter Children.
LET OP zorg dat je bij het uitvouwen alleen de kolommen Children en Text meeneemt- Kies nog 3x Uitvouwen met als resultaat:
- Selecteer de kolommen Text.4 en Text.3 en kies op de menutab Transformeren de optie Kolommen samenvoegen. Geef de nieuwe kolom de naam Artikel.
- Verwijder alle rijen, die we niet nodig hebben door er rechts op te klikken:
- Verwijder alle overbodige kolommen, geef de query de naam q_Tips1 en kies in de menutab Startpagina de optie Sluiten en laden.
Dat is al gelukt (zie het tabblad Tips1 van het voorbeeldbestand): we hebben de belangrijkste gegevens van de internetpagina in Excel.
Maar het zou mooier zijn als de kop van het artikel en de inhoud naast elkaar zou staan.
Van rijen naar kolommen
De methode die we hiervoor gaan gebruiken is een alternatief voor die uit het vorige artikel:
- Dubbelklik op de query q_Tips1.
- Kies in de menutab Kolom toevoegen de optie Indexkolom en direct daarna binnen Standaard de optie Delen (geheel getal) .
Vul in het tussenscherm de waarde 2 in en geef de nieuwe kolom de naam Rij. - Met de kolom Index geselecteerd, kies opnieuw Standaard, maar dan de optie Modulo; zorg dat door 2 gedeeld wordt.
Geef deze kolom de naam Kolom.
NB Met Modulo wordt de rest berekend van een deling. - Verwijder de kolom Index.
- Selecteer de kolom Kolom, kies in de menutab Transformeren de optie Draaikolom en vul het vervolgscherm als volgt in:
- Verwijder de kolom met de naam Rij en wijzig de 2 overblijvende kolomnamen in Naam en Inhoud. Kies de optie Sluiten en laden (zie het tabblad Tips2 van het voorbeeldbestand).
NB pas wel even de cel-eigenschappen van de tabel aan: kies Terugloop bij de Tekstopties en de optie Boven bij Verticaal.
Query als functie gebruiken
De query, q_Tips2, die we hiervoor hebben gemaakt, willen we nu voor verschillende internetpagina’s van G-Info gebruiken. Hij moet zich dus als een functie gedragen, waar we door middel van een parameter kunnen aangeven, welke pagina moet worden opgehaald:
- Kies in de menutab Gegevens in het blok Gegevens ophalen de optie Power Query-editor starten.
- Selecteer aan de linkerkant de query q_Tips2
en kies de optie Geavanceerde editor in de menutab Startpagina.
- Kopieer alle stappen van deze query; kies Annuleren.
- Klik met de rechter muisknop in de linkerkolom en voeg een Lege query toe:
- Ga weer naar de Geavanceerde editor en plak de gekopieerde stappen uit punt 2.
- Voeg vooraan een regel toe:
let AllePaginas=(PagNr) => - Wijzig de Bron-stap in:
Bron = Web.Page(Web.Contents(“ginfo.nl/tips-trucs/page/”&Number.ToText(PagNr))), - Voeg nog een laatste regel toe:
in AllePaginas - Klik op Gereed. Test de functie door als parameter een 1, 2 etc in te tikken en dan Aanroepen te kiezen.
- Geef de functie de naam fAllePags en kies de optie Sluiten en laden.
Functie gebruiken
De functie die we net gemaakt hebben gaan we gebruiken om met behulp van Power Query alle tips van G-Info tegelijkertijd op te halen:
- Allereerst moeten we aangeven welke paginanummers we willen importeren. We leggen dat in een Excel-tabel vast (met de naam tblPagNrs; zie het tabblad AlleTips van het voorbeeldbestand)
- Klik ergens in die tabel en kies in de menutab Gegevens in het blok Gegevens ophalen en transformeren de optie Van tabel/bereik.
- Kies in de menutab Startpagina de optie Kolom splitsen/Op scheidingsteken en vul het scherm als volgt in:
NB we splitsen in 50 kolommen zodat bij uitbreiding van het aantal tips-pagina’s de routine goed blijft werken. - Kies in de menutab Transformeren de optie Transponeren.
- Filter nu de lege rijen uit (rechtsklikken op een lege cel).
- Wijzig de kolomnaam in Pagina.
- Kies in de menutab Kolom toevoegen de optie Aangepaste functie aanroepen. Kies in het vervolgscherm bij Functiequery onze hiervoor gecreëerde functie; de rest van de opties zijn oké:
LET OP de routine gaat nu de gegevens van alle pagina’s ophalen; dat kost even wat tijd! - Vouw de kolom fAllePags uit.
- Verwijder de kolom Pagina.
- Geef de query de naam q_AlleTips en kies Sluiten en laden naar.
Helaas, we hebben bij het maken van de tips-query iets over het hoofd gezien: als er reacties zijn op een artikel dan ‘raakt het systeem in de war’ (zie het tabblad AlleTips van het voorbeeldbestand).
Ook staat er ergens nog zoiets als Een reactie tot dusver.
Bij het filteren van regels moeten we ook de reacties weglaten:
- Kies in de menutab Gegevens in het blok Gegevens ophalen de optie Power Query-editor starten.
- Klik rechts op de functie fAllePags en kies Dupliceren; wijzig de naam van de nieuwe functie in fAllePags2.
- Kies de optie Geavanceerde editor.
- Voeg als volgt na gefilterd4 2 stappen toe en wijzig de stap daarna:
#”Rijen gefilterd4″ = Table.SelectRows(#”Rijen gefilterd3″, each [Name] <> “nav”),
#”Rijen gefilterd5″ = Table.SelectRows(#”Rijen gefilterd4″, each not Text.Contains([Artikel], ” reactie tot dusver”)),
#”Rijen gefilterd6″ = Table.SelectRows(#”Rijen gefilterd5″, each not Text.Contains([Artikel], ” reacties bekijken”)),
#”Andere kolommen verwijderd” = Table.SelectColumns(#”Rijen gefilterd6“,{“Artikel”}), - Klik op Gereed.
- Test de nieuwe functie door deze aan te roepen met als parameter bijvoorbeeld 3.
- Kies de optie Sluiten en laden.
NB Door de paginanummers op het tabblad AlleTips2 van het voorbeeldbestand te wijzigen en de tabel daaronder te Vernieuwen kun je het overzicht aanpassen aan je wensen.