Power Query: If-instructie - geneste ifs & meerdere voorwaarden

In Excel Power Query is de IF-instructie een van de meest populaire functies om een voorwaarde te controleren en een specifieke waarde terug te geven, afhankelijk van of het resultaat WAAR of ONWAAR is. Er zijn enkele verschillen tussen deze if-instructie en de IF-functie van Excel. In deze tutorial introduceer ik de syntaxis van deze if-instructie en enkele eenvoudige en complexe voorbeelden voor u.

Basis if-instructie syntaxis van Power Query

Power Query if-instructie met behulp van een voorwaardelijke kolom

• Voorbeeld 1: Basis if-instructie
• Voorbeeld 2: Complexe if-instructie

Power Query if-instructie door M-code te schrijven

• Voorbeeld 1: Basis if-instructie
• Voorbeeld 2: Complexe if-instructie
  • Geneste if-instructies
  • If-instructie met OF-logica
  • If-instructie met EN-logica
  • If-instructie met OF- en EN-logica

Basis if-instructie syntaxis van Power Query

In Power Query is de syntaxis als volgt:

• logische_test: De voorwaarde die u wilt testen.
• waarde_als_waar: De waarde die wordt teruggegeven als het resultaat WAAR is.
• waarde_als_onwaar: De waarde die wordt teruggegeven als het resultaat ONWAAR is.

In Excel Power Query zijn er twee manieren om dit type voorwaardelijke logica te maken:

• Het gebruik van de Voorwaardelijke Kolom-functie voor enkele basis scenario's;
• Het schrijven van M-code voor meer geavanceerde scenario's.

In de volgende sectie zal ik het hebben over enkele voorbeelden voor het gebruik van deze if-instructie.

Power Query if-instructie met behulp van een voorwaardelijke kolom

 Voorbeeld 1: Basis if-instructie

Hier introduceer ik hoe u deze if-instructie kunt gebruiken in Power Query. Bijvoorbeeld, ik heb het volgende productrapport: als de productstatus 'Oud' is, toon dan een korting van 50%; als de productstatus 'Nieuw' is, toon dan een korting van 20%, zoals te zien is in de onderstaande screenshots.

1. Selecteer de datatabel uit het werkblad, klik vervolgens in Excel 2019 en Excel 365 op Gegevens > Van Tabel/Bereik, zie screenshot:

Opmerking: In Excel 2016 en Excel 2021, klik op Gegevens > Van Tabel, zie screenshot:

2. Vervolgens, in het geopende Power Query Editor-venster, klik op Kolom Toevoegen > Voorwaardelijke Kolom, zie screenshot:

3. Voer in het pop-upvenster Voorwaardelijke Kolom Toevoegen de volgende handelingen uit:

• Nieuwe Kolom Naam: Voer een naam in voor de nieuwe kolom;
• Specificeer vervolgens de criteria die u nodig hebt. Bijvoorbeeld, ik specificeer Als Status gelijk is aan Oud dan 50% anders 20%;

4. Klik vervolgens op de knop OK om terug te keren naar het Power Query Editor-venster. Nu is er een nieuwe Korting-kolom toegevoegd, zie screenshot:

5. Als u de getallen wilt formatteren als percentages, klikt u gewoon op het ABC123-pictogram in de kolomkop van Korting en kiest u Percentage indien nodig, zie screenshot:

6. Klik ten slotte op Start > Sluiten en Laden > Sluiten en Laden om deze gegevens naar een nieuw werkblad te laden.

 Voorbeeld 2: Complexe if-instructie

Met deze Voorwaardelijke Kolom-optie kunt u ook twee of meer voorwaarden invoegen in het dialoogvenster Voorwaardelijke Kolom Toevoegen. Doe het volgende:

1. Selecteer de datatabel en ga naar het Power Query Editor-venster door te klikken op Gegevens > Van Tabel/Bereik. Klik in het nieuwe venster op Kolom Toevoegen > Voorwaardelijke Kolom.

2. Voer in het pop-upvenster Voorwaardelijke Kolom Toevoegen de volgende handelingen uit:

• Voer een naam in voor de nieuwe kolom in het tekstvak Nieuwe kolom naam;
• Specificeer de eerste criteria in het eerste criteria-veld en klik vervolgens op de knop Clause Toevoegen om andere criteria-velden toe te voegen indien nodig.

3. Nadat u de criteria hebt voltooid, klikt u op de knop OK om terug te keren naar het Power Query Editor-venster. U krijgt nu een nieuwe kolom met het bijbehorende resultaat dat u nodig hebt. Zie screenshot:

4. Ten slotte klikt u op Start > Sluiten en Laden > Sluiten en Laden om deze gegevens naar een nieuw werkblad te laden.

Power Query if-instructie door M-code te schrijven

Normaal gesproken is Voorwaardelijke Kolom handig voor enkele basis scenario's. Soms moet u mogelijk meerdere voorwaarden gebruiken met EN- of OF-logica. In dat geval moet u M-code schrijven binnen een Aangepaste Kolom voor meer complexe scenario's.

 Voorbeeld 1: Basis if-instructie

Neem de eerste dataset als voorbeeld: als de productstatus 'Oud' is, toon dan een korting van 50%; als de productstatus 'Nieuw' is, toon dan een korting van 20%. Om de M-code te schrijven, doe het volgende:

1. Selecteer de tabel en klik op Gegevens > Van Tabel/Bereik om naar het Power Query Editor-venster te gaan.

2. Klik in het geopende venster op Kolom Toevoegen > Aangepaste Kolom, zie screenshot:

3. Voer in het pop-upvenster Aangepaste Kolom de volgende handelingen uit:

• Voer een naam in voor de nieuwe kolom in het tekstvak Nieuwe kolom naam;
• Voer vervolgens deze formule in: if [Status] = "Oud " then "50% " else "20% " in het tekstvak Aangepaste kolom formule.

4. Klik vervolgens op OK om dit dialoogvenster te sluiten. U krijgt nu het volgende resultaat zoals u nodig hebt:

5. Ten slotte klikt u op Start > Sluiten en Laden > Sluiten en Laden om deze gegevens naar een nieuw werkblad te laden.

 Voorbeeld 2: Complexe if-instructie

Geneste if-instructies

Om subvoorwaarden te testen, kunt u meerdere if-instructies nesten. Bijvoorbeeld, ik heb de onderstaande datatabel. Als het product 'Jurk' is, geef dan 50% korting op de oorspronkelijke prijs; als het product 'Trui' of 'Hoodie' is, geef dan 20% korting op de oorspronkelijke prijs; en andere producten houden de oorspronkelijke prijs.

1. Selecteer de datatabel en klik op Gegevens > Van Tabel/Bereik om naar het Power Query Editor-venster te gaan.

2. Klik in het geopende venster op Kolom Toevoegen > Aangepaste Kolom. Voer in het geopende dialoogvenster Aangepaste Kolom de volgende handelingen uit:

• Voer een naam in voor de nieuwe kolom in het tekstvak Nieuwe kolom naam;
• Voer vervolgens de onderstaande formule in in het Aangepaste kolom formule tekstvak.
• = if [Product] = "Jurk" then [Prijs] * 0.5 else
  if [Product] = "Trui" then [Prijs] * 0.8 else
  if [Product] = "Hoodie" then [Prijs] * 0.8
  else [Prijs]

3. Klik vervolgens op de knop OK om terug te keren naar het Power Query Editor-venster, en u krijgt een nieuwe kolom met de gegevens die u nodig hebt, zie screenshot:

4. Ten slotte klikt u op Start > Sluiten en Laden > Sluiten en Laden om deze gegevens naar een nieuw werkblad te laden.

If-instructie met OF-logica

De OF-logica voert meerdere logische tests uit, en het ware resultaat wordt geretourneerd als een van de logische tests waar is. De syntaxis is:

Stel dat ik de onderstaande tabel heb, nu wil ik een nieuwe kolom weergeven als: als het product 'Jurk' of 'T-shirt' is, dan is het merk 'AAA', het merk van andere producten is 'BBB'.

1. Selecteer de datatabel en klik op Gegevens > Van Tabel/Bereik om naar het Power Query Editor-venster te gaan.

2. Klik in het geopende venster op Kolom Toevoegen > Aangepaste Kolom, voer in het geopende dialoogvenster Aangepaste Kolom de volgende handelingen uit:

• Voer een naam in voor de nieuwe kolom in het tekstvak Nieuwe kolom naam;
• Voer vervolgens de onderstaande formule in in het tekstvak Aangepaste kolom formule.
• = if [Product] = "Jurk" of [Product] = "T-shirt" then "AAA"
  else "BBB"

3. Klik vervolgens op de knop OK om terug te keren naar het Power Query Editor-venster, en u krijgt een nieuwe kolom met de gegevens die u nodig hebt, zie screenshot:

4. Ten slotte klikt u op Start > Sluiten en Laden > Sluiten en Laden om deze gegevens naar een nieuw werkblad te laden.

If-instructie met EN-logica

De EN-logica voert meerdere logische tests uit binnen een enkele if-instructie. Alle tests moeten waar zijn om het ware resultaat te retourneren. Als een van de tests onwaar is, wordt het onware resultaat geretourneerd. De syntaxis is:

Neem de bovenstaande gegevens als voorbeeld. Ik wil een nieuwe kolom weergeven: als het product 'Jurk' is en de bestelling groter is dan 300, pas dan een korting van 50% toe op de oorspronkelijke prijs; anders blijft de oorspronkelijke prijs gehandhaafd.

1. Selecteer de datatabel en klik op Gegevens > Van Tabel/Bereik om naar het Power Query Editor-venster te gaan.

2. Klik in het geopende venster op Kolom Toevoegen > Aangepaste Kolom. Voer in het geopende dialoogvenster Aangepaste Kolom de volgende handelingen uit:

• Voer een naam in voor de nieuwe kolom in het tekstvak Nieuwe kolom naam;
• Voer vervolgens de onderstaande formule in in het tekstvak Aangepaste kolom formule.
• = if [Product] ="Jurk" en [Bestelling] > 300 then [Prijs]*0.5
  else [Prijs]

3. Klik vervolgens op de knop OK om terug te keren naar het Power Query Editor-venster, en u krijgt een nieuwe kolom met de gegevens die u nodig hebt, zie screenshot:

4. Ten slotte laadt u deze gegevens naar een nieuw werkblad door te klikken op Start > Sluiten en Laden > Sluiten en Laden.

If-instructie met OF- en EN-logica

Goed, de vorige voorbeelden zijn gemakkelijk te begrijpen. Laten we het nu moeilijker maken. U kunt EN en OF combineren om elke denkbare voorwaarde te vormen. In dit type kunt u haakjes in de formule gebruiken om complexe regels te definiëren.

Neem de bovenstaande gegevens als voorbeeld. Stel dat ik een nieuwe kolom wil weergeven als: als het product 'Jurk' is en de bestelling groter is dan 300, of als het product 'Broek' is en de bestelling groter is dan 300, dan toon 'A+', anders toon 'Overig'.

1. Selecteer de datatabel en klik op Gegevens > Van Tabel/Bereik om naar het Power Query Editor-venster te gaan.

2. Klik in het geopende venster op Kolom Toevoegen > Aangepaste Kolom. Voer in het geopende dialoogvenster Aangepaste Kolom de volgende handelingen uit:

• Voer een naam in voor de nieuwe kolom in het tekstvak Nieuwe kolom naam;
• Voer vervolgens de onderstaande formule in in het tekstvak Aangepaste kolom formule.
• =if ([Product] = "Jurk" en [Bestelling] > 300 ) of
  ([Product] = "Broek" en [Bestelling] > 300 )
  then "A+"
  else "Overig"

3. Klik vervolgens op de knop OK om terug te keren naar het Power Query Editor-venster, en u krijgt een nieuwe kolom met de gegevens die u nodig hebt, zie screenshot:

4. Ten slotte laadt u deze gegevens naar een nieuw werkblad door te klikken op Start > Sluiten en Laden > Sluiten en Laden.