Training
Module
Ingebouwde functies en GROUP BY gebruiken in Transact-SQL - Training
Ingebouwde functies en GROUP BY gebruiken in Transact-SQL
Deze browser wordt niet meer ondersteund.
Upgrade naar Microsoft Edge om te profiteren van de nieuwste functies, beveiligingsupdates en technische ondersteuning.
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)
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.
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.
Met deze bewerking krijgt u de volgende tabel.
Met de functie Groeperen op kunnen de beschikbare bewerkingen op twee manieren worden gecategoriseerd:
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.
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:
Maak twee nieuwe kolommen door de volgende stappen uit te voeren:
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.
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.
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:
In dit voorbeeld wordt een transformatietabel gebruikt om te laten zien hoe waarden kunnen worden toegewezen. De transformatietabel heeft twee kolommen:
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.
Training
Module
Ingebouwde functies en GROUP BY gebruiken in Transact-SQL - Training
Ingebouwde functies en GROUP BY gebruiken in Transact-SQL