Power BI: les 3 van 9, Power Query: data opschonen

Power Query: data opschonen in Power BI

Noor opent de verkoopbestanden van Bonenberg en ziet meteen rommel: dubbele regels, een leeg klantnummer en productcodes met een spatie. Met Power Query ruim je dat op, zonder dat het bronbestand verandert. In deze les doe je dat stap voor stap.

Je ruimt in Power Query de verkopen op, stap voor stap.

Wat is Power Query?

Power Query is het onderdeel waarmee je gegevens ophaalt en klaarmaakt voordat ze in je rapport komen. Het werkt in Excel (daar vind je het onder het tabblad Gegevens) en in Power BI volgens hetzelfde idee. Het bijzondere is dat elke handeling een toegepaste stap (Applied steps) wordt. Die stappen staan in een lijst rechts in het scherm. Bij het vernieuwen voert Power BI ze allemaal opnieuw uit, in dezelfde volgorde.

Je bronbestanden worden daarbij nooit aangepast. Achter de stappen staat een taal die M heet. Die hoef je niet te kennen. Klik in het voorbeeld hieronder op de stappen en kijk wat er met de tabel gebeurt.

Zo ruimt Power Query de regels van Bonenberg op
Power Query-editorVerkopen

Bron: De regels zoals ze in de CSV-bestanden staan, met alle fouten.

OrdernummerOrderdatumKlantIDProductIDAantalStukprijs
1B26-107601-02-2026K024P021021,60
2B26-102204-01-2026K028P01921,50
3B26-102204-01-2026K028P01921,50
4B26-106108-02-2026P01321,50
5B26-110102-03-2026K011 p0624,25
6B26-112807-03-2026K017p01 1219,35
7nullnullnullnullnullnull

Type gewijzigd: Datums en bedragen zijn nu echt een datum en een getal, met Landinstelling Nederlands (Nederland).

OrdernummerOrderdatumKlantIDProductIDAantalStukprijs
1B26-107601-02-2026K024P021021,60
2B26-102204-01-2026K028P01921,50
3B26-102204-01-2026K028P01921,50
4B26-106108-02-2026P01321,50
5B26-110102-03-2026K011 p0624,25
6B26-112807-03-2026K017p01 1219,35
7nullnullnullnullnullnull

Lege rijen verwijderd: De regel waar in elke kolom niets staat is weg. Let op: regel 4 blijft, want daar mist alleen het KlantID.

OrdernummerOrderdatumKlantIDProductIDAantalStukprijs
1B26-107601-02-2026K024P021021,60
2B26-102204-01-2026K028P01921,50
3B26-102204-01-2026K028P01921,50
4B26-106108-02-2026P01321,50
5B26-110102-03-2026K011 p0624,25
6B26-112807-03-2026K017p01 1219,35

Bijgesneden: Bijsnijden haalt de spaties voor en achter de tekst weg in ProductID.

OrdernummerOrderdatumKlantIDProductIDAantalStukprijs
1B26-107601-02-2026K024P021021,60
2B26-102204-01-2026K028P01921,50
3B26-102204-01-2026K028P01921,50
4B26-106108-02-2026P01321,50
5B26-110102-03-2026K011p0624,25
6B26-112807-03-2026K017p011219,35

HOOFDLETTERS: Nu staat er overal P01 en P06 in plaats van p01 en p06.

OrdernummerOrderdatumKlantIDProductIDAantalStukprijs
1B26-107601-02-2026K024P021021,60
2B26-102204-01-2026K028P01921,50
3B26-102204-01-2026K028P01921,50
4B26-106108-02-2026P01321,50
5B26-110102-03-2026K011P0624,25
6B26-112807-03-2026K017P011219,35

Waarde vervangen: De lege KlantID is vervangen door K000, de onbekende klant.

OrdernummerOrderdatumKlantIDProductIDAantalStukprijs
1B26-107601-02-2026K024P021021,60
2B26-102204-01-2026K028P01921,50
3B26-102204-01-2026K028P01921,50
4B26-106108-02-2026K000P01321,50
5B26-110102-03-2026K011P0624,25
6B26-112807-03-2026K017P011219,35

Duplicaten verwijderd: De tweede, identieke regel van B26-1022 is weg. De rest blijft.

OrdernummerOrderdatumKlantIDProductIDAantalStukprijs
1B26-107601-02-2026K024P021021,60
2B26-102204-01-2026K028P01921,50
3B26-106108-02-2026K000P01321,50
4B26-110102-03-2026K011P0624,25
5B26-112807-03-2026K017P011219,35

Toegepaste stappen

Een paar echte regels uit de oefenbestanden, bij elkaar gezet om alle fouten in één keer te laten zien.

Stap voor stap opschonen

Je doet nu hetzelfde met het januaribestand. Dat is het bestand met de dubbele regels.

  1. Kies op het tabblad Start Gegevens ophalen (Get data), dan Tekst/CSV en kies verkopen-2026-01.csv. Klik op Gegevens transformeren (Transform data). De Power Query-editor opent.
  2. Kijk rechts bij Toegepaste stappen. Power BI heeft zelf al stappen gezet, zoals de eerste rij als kolomkop gebruiken. Klopt een datum of bedrag niet? Klik dan op het kruisje bij de stap Type gewijzigd om hem te verwijderen.
  3. Datum en bedrag: klik met rechts op de kop van Orderdatum. Kies Type wijzigen (Change type), dan Landinstelling gebruiken (Using locale). Kies als gegevenstype Datum en als landinstelling Nederlands (Nederland). Doe hetzelfde voor Stukprijs met als type Decimaal getal. Zet Aantal op Geheel getal.
  4. Lege rijen: kies op het tabblad Start Rijen verwijderen (Remove rows), dan Lege rijen verwijderen (Remove blank rows).
  5. Spaties en hoofdletters: selecteer de kolom ProductID. Kies op het tabblad Transformeren bij Opmaak (Format) eerst Bijsnijden (Trim) en daarna HOOFDLETTERS (UPPERCASE).
  6. Lege KlantID: selecteer de kolom KlantID en kies Waarden vervangen (Replace values). Laat "Te zoeken waarde" leeg (dan zoekt Power Query naar een leeg veld) en typ bij "Vervangen door" K000. Staat er in de cel het woord null in plaats van niets? Typ dan bij "Te zoeken waarde" null.
  7. Duplicaten: selecteer alle kolommen (klik op de eerste kolomkop, houd Shift ingedrukt en klik op de laatste). Klik met rechts op een kolomkop en kies Duplicaten verwijderen (Remove duplicates).

Waarom die volgorde?

Duplicaten verwijder je als laatste. Een regel telt alleen als dubbel als alle kolommen gelijk zijn. Staat in de ene regel P01 en in de andere p01 , dan zijn ze voor Power Query niet gelijk. Eerst bijsnijden en hoofdletters, dan pas duplicaten. Daarom kies je ook alle kolommen: alleen dan weet je zeker dat het echt dezelfde verkoopregel is. Twee regels met hetzelfde ordernummer mogen blijven bestaan, want een bestelling heeft meerdere producten.

Je kunt elke stap terugkijken. Klik op een stap in de lijst Toegepaste stappen en je ziet de tabel zoals hij toen was. Je kunt een stap ook verwijderen of slepen naar een andere plek.

Probeer het zelf

Controleer jouw januari. Kijk onderaan in de Power Query-editor naar het aantal rijen.

Klopt het? Je ziet nu voor januari 76 regels. In het bestand stonden 78 regels met gegevens, dus de 2 dubbele regels (B26-1022 en B26-1006) zijn weg. Wil je extra oefenen? Doe dezelfde stappen met de bestanden van februari en maart. Je ziet dan februari: 63 regels en maart: 94 regels. Het aantal regels verandert daar niet, want een lege KlantID of een spatie in een ProductID haalt geen regel weg. In les 4 doe je alle drie de maanden in één keer.

MaandRegels in het bestandRegels na opschonenOmzet na opschonen
januari7876€ 5.516,35
februari6363€ 4.486,95
maart9494€ 6.754,80

De omzet per maand kun je nog niet zelf uitrekenen. Dat komt in les 5 en les 6. Dan kun je deze getallen weer nalopen.

Veelgemaakte fout

De duplicaten niet verwijderen. Het rapport laat dan geen foutmelding zien, maar de cijfers kloppen niet. Met de dubbele regels erin is de omzet van januari tot en met maart € 425,70 te hoog. Is jouw uitkomst later precies zoveel hoger dan de controlewaarde, kijk dan eerst naar je stap Duplicaten verwijderen.

Onthoud

Check jezelf

Wat gebeurt er met je bronbestand als je in Power Query opschoont?

Niets. Het bestand blijft zoals het was.

Waarom kies je bij Duplicaten verwijderen alle kolommen?

Alleen dan verdwijnt een regel als de hele regel gelijk is aan een andere regel.

Je omzet is € 425,70 te hoog. Wat is een waarschijnlijke oorzaak?

De dubbele regels zijn niet verwijderd.

Ken je iemand die dit ook kan gebruiken? Deel de cursus.

Deel via WhatsApp

LinkedIn Facebook E-mail

Veelgestelde vragen

Wat is Power Query?

Power Query is het onderdeel van Power BI waarmee je gegevens ophaalt en opschoont. Elke handeling wordt een stap in een lijst. Bij vernieuwen voert Power BI die stappen opnieuw uit. Power Query zit ook in Excel, daar werkt het volgens hetzelfde idee.

Past Power Query mijn bronbestand aan?

Nee. Het bronbestand blijft zoals het is. Power Query past alleen de kopie aan die in je rapport terechtkomt.

Hoe verwijder je dubbele regels in Power Query?

Selecteer alle kolommen, klik met de rechtermuisknop op een kolomkop en kies Duplicaten verwijderen (Remove duplicates). Een regel verdwijnt alleen als alle gekozen kolommen gelijk zijn aan een regel erboven.

Wat doe ik als een komma als decimaalteken verkeerd wordt gelezen?

Gebruik Type wijzigen en dan Landinstelling gebruiken (Using locale) en kies Nederlands (Nederland). Dan weet Power BI dat 21,50 een bedrag is.

Aangeraden door