Rijen groeperen of samenvatten
In Power Query kunt u waarden in verschillende rijen groeperen in één waarde door de rijen te groeperen op basis van de waarden in een of meer kolommen. U kunt kiezen uit twee typen groeperingsbewerkingen:
Kolomgroepen.
Rijgroepen.
Voor deze zelfstudie gebruikt u de volgende voorbeeldtabel.
Schermopname van een tabel met kolommen met Year (2020), Country (USA, Panama of Canada), Product (Shirt of Shorts), Verkoopkanaal (Online of Reseller) en Eenheden (verschillende waarden tussen 55 en 7500)
Waar vind ik de knop Groeperen op
U vindt de knop Groeperen op drie plaatsen:
Ga naar het tabblad Start , in de groep Transformeren .
Klik op het tabblad Transformeren in de groep Tabel .
Klik in het snelmenu met de rechtermuisknop om kolommen te selecteren.
Een statistische functie gebruiken om te groeperen op een of meer kolommen
In dit voorbeeld is het uw doel om het totale aantal verkochte eenheden op land- en verkoopkanaalniveau samen te vatten. U gebruikt de kolommen Land - en Verkoopkanaal om de groep per bewerking uit te voeren.
- Selecteer Groeperen op op het tabblad Start .
- Selecteer de optie Geavanceerd , zodat u meerdere kolommen kunt selecteren waarop u wilt groeperen.
- Selecteer de kolom Land .
- Selecteer Groeperen toevoegen.
- Selecteer de kolom Verkoopkanaal .
- Voer in Nieuwe kolomnaam totaaleenheden in, in Bewerking, selecteer Som en selecteer Eenheden in Kolom.
- Selecteer OK.
Met deze bewerking krijgt u de volgende tabel.
Beschikbare bewerkingen
Met de functie Groeperen op kunnen de beschikbare bewerkingen op twee manieren worden gecategoriseerd:
- Bewerking op rijniveau
- Bewerking op kolomniveau
In de volgende tabel wordt elk van deze bewerkingen beschreven.
| Naam van bewerking | Categorie | Beschrijving |
|---|---|---|
| Sum | Kolombewerking | Somt alle waarden van een kolom op |
| Average | Kolombewerking | Berekent de gemiddelde waarde van een kolom |
| Mediaan | Kolombewerking | Berekent de mediaan van een kolom |
| Min | Kolombewerking | Berekent de minimumwaarde van een kolom |
| Max | Kolombewerking | Berekent de maximumwaarde van een kolom |
| Percentiel | Kolombewerking | Berekent het percentiel met behulp van een invoerwaarde van 0 tot 100, van een kolom |
| Afzonderlijke waarden tellen | Kolombewerking | Berekent het aantal afzonderlijke waarden uit een kolom |
| Rijen tellen | Rijbewerking | Berekent het totale aantal rijen uit een bepaalde groep |
| Afzonderlijke rijen tellen | Rijbewerking | Berekent het aantal afzonderlijke rijen uit een bepaalde groep |
| Alle rijen | Rijbewerking | Hiermee worden alle gegroepeerde rijen in een tabelwaarde uitgevoerd zonder aggregaties |
Notitie
De afzonderlijke waarden tellen en percentielbewerkingen zijn alleen beschikbaar in Power Query Online.
Een bewerking uitvoeren om te groeperen op een of meer kolommen
Vanaf het oorspronkelijke voorbeeld maakt u in dit voorbeeld een kolom met de totale eenheden en twee andere kolommen die u de naam en eenheden geven die zijn verkocht voor het best presterende product, samengevat op het niveau van het land en verkoopkanaal.
Gebruik de volgende kolommen als Groeperen op kolommen:
- Land/regio
- Verkoopkanaal
Maak twee nieuwe kolommen door de volgende stappen uit te voeren:
- De kolom Eenheden aggregeren met behulp van de bewerking Som. Geef deze kolom de naam Totaaleenheden.
- Voeg een nieuwe kolom Producten toe met behulp van de bewerking Alle rijen .
Nadat deze bewerking is voltooid, ziet u hoe de kolom Producten [Tabel] waarden bevat in elke cel. Elke [Tabel]-waarde bevat alle rijen die zijn gegroepeerd op de kolommen Land en Verkoopkanaal uit de oorspronkelijke tabel. U kunt de witruimte in de cel selecteren om een voorbeeld van de inhoud van de tabel onder aan het dialoogvenster weer te geven.
Notitie
In het voorbeeldvenster details worden mogelijk niet alle rijen weergegeven die zijn gebruikt voor de groepeerbewerking. U kunt de waarde [Tabel] selecteren om alle rijen weer te geven die betrekking hebben op de bijbehorende groepeerbewerking.
Vervolgens moet u de rij extraheren met de hoogste waarde in de kolom Eenheden van de tabellen in de nieuwe kolom Producten en die nieuwe kolom Top performerproduct aanroepen.
De belangrijkste productinformatie van de performer extraheren
Met de nieuwe kolom Producten met [Tabel]-waarden maakt u een nieuwe aangepaste kolom door naar het tabblad Kolom toevoegen op het lint te gaan en Aangepaste kolom te selecteren in de groep Algemeen.
Geef uw nieuwe kolom top-performerproduct een naam. Voer de formule Table.Max([Products], "Units" ) onder Aangepaste kolomformule in.
Het resultaat van die formule maakt een nieuwe kolom met [Record]-waarden. Deze recordwaarden zijn in feite een tabel met slechts één rij. Deze records bevatten de rij met de maximumwaarde voor de kolom Eenheden van elke [Tabel]-waarde in de kolom Producten .
Met deze nieuwe kolom Product voor top-performer die [Record] waarden bevat, kunt u het
uitvouwpictogram selecteren, de velden Product en Eenheden selecteren en vervolgens OK selecteren.
Nadat u de kolom Producten hebt verwijderd en het gegevenstype voor beide nieuw uitgevouwen kolommen hebt ingesteld, lijkt het resultaat op de volgende afbeelding.
Fuzzy groepering
Notitie
De volgende functie is alleen beschikbaar in Power Query Online.
Bekijk de voorbeeldtabel in de volgende afbeelding om te laten zien hoe u 'fuzzy groepering' kunt uitvoeren.
Het doel van fuzzy groepering is om een group-by-bewerking uit te voeren die gebruikmaakt van een benaderingsalgoritme voor teksttekenreeksen. Power Query maakt gebruik van het jaccard-gelijkenis-algoritme om de gelijkenis tussen paren exemplaren te meten. Vervolgens wordt agglomeratieve hiërarchische clustering toegepast op groepexemplaren samen. In de volgende afbeelding ziet u de uitvoer die u verwacht, waarbij de tabel is gegroepeerd op de kolom Persoon .
Als u de fuzzy groepering wilt uitvoeren, voert u dezelfde stappen uit die eerder in dit artikel zijn beschreven. Het enige verschil is dat u in het dialoogvenster Groeperen op het selectievakje Fuzzy groepering gebruiken inschakelt.
Voor elke groep rijen kiest Power Query het meest voorkomende exemplaar als het 'canonieke' exemplaar. Als er meerdere exemplaren met dezelfde frequentie optreden, kiest Power Query de eerste. Nadat u OK hebt geselecteerd in het dialoogvenster Groeperen op, krijgt u het resultaat dat u verwachtte.
U hebt echter meer controle over de fuzzy groeperingsbewerking door opties voor Fuzzy-groepen uit te vouwen.
De volgende opties zijn beschikbaar voor fuzzy groepering:
- Vergelijkbaarheidsdrempel (optioneel): met deze optie wordt aangegeven hoe vergelijkbare twee waarden moeten worden gegroepeerd. De minimuminstelling nul (0) zorgt ervoor dat alle waarden worden gegroepeerd. Met de maximuminstelling van 1 kunnen alleen waarden worden gegroepeerd die exact overeenkomen. De standaardwaarde is 0,8.
- Hoofdlettergebruik: wanneer teksttekenreeksen worden vergeleken, wordt hoofdlettergebruik genegeerd. Deze optie is standaard ingeschakeld.
- Groeperen door tekstonderdelen te combineren: het algoritme probeert tekstonderdelen (zoals micro en zacht in Microsoft) te combineren om waarden te groeperen.
- Overeenkomstenscores weergeven: Overeenkomstenscores weergeven tussen de invoerwaarden en de berekende representatieve waarden na fuzzy groepering. Vereist het toevoegen van een bewerking, zoals Alle rijen , om deze informatie op rijniveau weer te geven.
- Transformatietabel (optioneel): u kunt een transformatietabel selecteren waarmee waarden (zoals MSFT aan Microsoft) worden toegewezen om ze te groeperen.
In dit voorbeeld wordt een transformatietabel gebruikt om te laten zien hoe waarden kunnen worden toegewezen. De transformatietabel heeft twee kolommen:
- Van: De tekenreeks waarnaar u in de tabel wilt zoeken.
- To: De tekenreeks die moet worden gebruikt om de tekenreeks in de kolom Van te vervangen.
In de volgende afbeelding ziet u de transformatietabel die in dit voorbeeld wordt gebruikt.
Belangrijk
Het is belangrijk dat de transformatietabel dezelfde kolommen en kolomnamen heeft als in de vorige afbeelding (ze moeten worden gelabeld als 'Van' en 'Aan'). Anders herkent Power Query de tabel niet als een transformatietabel.
Ga terug naar het dialoogvenster Groeperen op, vouw fuzzy groepsopties uit, wijzig de bewerking van aantal rijen in Alle rijen, schakel de optie Overeenkomstenscores weergeven in en selecteer vervolgens de vervolgkeuzelijst Transformatietabel .
Nadat u de transformatietabel hebt geselecteerd, selecteert u OK. Het resultaat van deze bewerking geeft u de volgende informatie:
In dit voorbeeld is de optie Hoofdletters negeren ingeschakeld, zodat de waarden in de kolom Van van de tabel Transformatie worden gebruikt om naar de tekenreeks te zoeken zonder rekening te houden met het hoofdlettergebruik van de tekenreeks. Deze transformatiebewerking vindt eerst plaats en vervolgens wordt de fuzzy groeperingsbewerking uitgevoerd.
De overeenkomstscore wordt ook weergegeven in de tabelwaarde naast de kolom persoon, die precies aangeeft hoe de waarden zijn gegroepeerd en de bijbehorende overeenkomsten. U kunt deze kolom indien nodig uitbreiden of de waarden uit de nieuwe frequentiekolommen gebruiken voor andere soorten transformaties.
Notitie
Wanneer u groepeert op meerdere kolommen, voert de transformatietabel de vervangingsbewerking uit in alle kolommen als het vervangen van de waarde de gelijkenisscore verhoogt.
Voor meer informatie over hoe transformatietabellen werken, gaat u naar De voorschriften voor transformatietabellen.