Gruppere eller oppsummere rader

  • Artikkel

I Power Query kan du gruppere verdier i forskjellige rader til én enkelt verdi ved å gruppere radene i henhold til verdiene i én eller flere kolonner. Du kan velge mellom to typer grupperingsoperasjoner:

  • Kolonnegrupperinger.

  • Radgrupperinger.

I denne opplæringen skal du bruke følgende eksempeltabell.

Eksempel på første tabell.

Her finner du Grupper etter-knappen

Du finner Grupper etter-knappen på tre steder:

  • I Transformer-gruppen på Hjem-fanen.

    Grupper etter på Hjem-fanen.

  • I Tabell-gruppen på Fanen Transformer.

    Grupper etter på Fanen Transformer.

  • På hurtigmenyen når du høyreklikker for å velge kolonner.

    Grupper etter på hurtigmenyen.

Bruke en mengdefunksjon til å gruppere etter én eller flere kolonner

I dette eksemplet er målet å oppsummere totalt antall solgte enheter på land- og salgskanalnivå. Du bruker kolonnene Land og Salgskanal til å utføre gruppen etter operasjon.

  1. Velg Grupper etter på Hjem-fanen .
  2. Velg avansert-alternativet, slik at du kan velge flere kolonner du vil gruppere etter.
  3. Velg Land-kolonnen.
  4. Velg Legg til gruppering.
  5. Velg Salgskanal-kolonnen.
  6. I Nytt kolonnenavn skriver du inn Totalt antall enheter, i Operasjon, velger Sum og velger Enheter i kolonne.
  7. Velg OK

Grupper etter dialogboks med aggregerte kolonner.

Denne operasjonen gir deg følgende tabell.

Eksempel på utdatatabell med kolonnene Land, Salgskanal og Totalt antall enheter.

Operasjoner tilgjengelig

Med funksjonen Grupper etter kan de tilgjengelige operasjonene kategoriseres på to måter:

  • Operasjon på radnivå
  • Operasjon på kolonnenivå

Tabellen nedenfor beskriver hver av disse operasjonene.

Operasjonsnavn Kategori Bekrivelse
Sum Kolonneoperasjon Oppsummerer alle verdier fra en kolonne
Average Kolonneoperasjon Beregner gjennomsnittsverdien fra en kolonne
Median Kolonneoperasjon Beregner medianen fra en kolonne
Min Kolonneoperasjon Beregner minimumsverdien fra en kolonne
Max Kolonneoperasjon Beregner maksimumsverdien fra en kolonne
Persentil Kolonneoperasjon Beregner persentilen ved hjelp av en inndataverdi fra 0 til 100, fra en kolonne
Telle distinkte verdier Kolonneoperasjon Beregner antall distinkte verdier fra en kolonne
Telle rader Radoperasjon Beregner totalt antall rader fra en gitt gruppe
Telle distinkte rader Radoperasjon Beregner antall distinkte rader fra en gitt gruppe
Alle rader Radoperasjon Sender ut alle grupperte rader i en tabellverdi uten aggregasjoner

Merk

Antall distinkte verdier og persentiloperasjoner er bare tilgjengelige i Power Query Online.

Utføre en operasjon for å gruppere etter én eller flere kolonner

Fra og med det opprinnelige eksemplet skal du i dette eksemplet opprette en kolonne som inneholder totalt antall enheter og to andre kolonner som gir deg navnet og enhetene som selges for produktet med beste ytelse, oppsummert på land- og salgskanalnivå.

Eksempel på utdatatabell med operasjoner.

  1. Bruk følgende kolonner som Grupper etter kolonner:

    • Land
    • Salgskanal

  2. Opprett to nye kolonner ved å følge disse trinnene:

    1. Aggreger Enheter-kolonnen ved hjelp av Sum-operasjonen. Gi navn til denne kolonnen Totalt antall enheter.
    2. Legg til en ny Produkter-kolonne ved hjelp av Alle rader-operasjonen .

    Grupper etter dialogboks med en kolonne som ikke er aggregert.

Når operasjonen er fullført, kan du legge merke til hvordan Produkter-kolonnen har [Tabell]-verdier i hver celle. Hver [Tabell]-verdi inneholder alle radene som ble gruppert etter kolonnene Land og Salgskanal fra den opprinnelige tabellen. Du kan velge mellomrom i cellen for å se en forhåndsvisning av innholdet i tabellen nederst i dialogboksen.

Forhåndsvisningsrute for tabelldetaljer.

Merk

Forhåndsvisningsruten for detaljer viser kanskje ikke alle radene som ble brukt for gruppe-etter-operasjonen. Du kan velge [Tabell]-verdien for å se alle rader som gjelder den tilsvarende group-by-operasjonen.

Deretter må du trekke ut raden som har den høyeste verdien i Enheter-kolonnen i tabellene i den nye Produkter-kolonnen, og kalle det nye kolonnen Top performer-produktet.

Trekke ut produktinformasjonen for den beste artisten

Med den nye Produkter-kolonnen med [Tabell]-verdier oppretter du en ny egendefinert kolonne ved å gå til Fanen Legg til kolonne på båndet og velge Egendefinert kolonne fra Generelt-gruppen .

Legg til en egendefinert kolonne.

Gi navn til det nye topartistproduktet for kolonnen. Skriv inn formelen Table.Max([Products], "Units" ) under Egendefinert kolonneformel.

Egendefinert kolonneformel med Table.Max.

Resultatet av denne formelen oppretter en ny kolonne med [Record]-verdier. Disse postverdiene er i hovedsak en tabell med bare én rad. Disse postene inneholder raden med maksimumsverdien for Enheter-kolonnen for hver [Tabell]-verdi i Produkter-kolonnen .

Resultatet av den egendefinerte kolonneformelen med Table.Max.

Med denne nye produktkolonnen for øverste artist som inneholder [Record]-verdier, kan du velgeUtvide.utvid-ikonet, velge feltene Produkt og Enheter og deretter velge OK.

Utvid operasjon for postverdi i produktkolonnen Top performer.

Når du har fjernet Produkter-kolonnen og angitt datatypen for begge nylig utvidede kolonner, vil resultatet ligne på følgende bilde.

Endelig tabell med alle transformasjoner.

Fuzzy gruppering

Merk

Følgende funksjon er bare tilgjengelig i Power Query Online.

Hvis du vil demonstrere hvordan du gjør «uklar gruppering», bør du vurdere eksempeltabellen som vises på bildet nedenfor.

Tabell med ni rader med oppføringer som inneholder ulike stavemåter og store bokstaver i navnet Miguel og William.

Målet med uklar gruppering er å utføre en grupperingsoperasjon som bruker en omtrentlig samsvarsalgoritme for tekststrenger. Power Query bruker algoritmen for jaccard-likhet til å måle likheten mellom forekomstpar. Deretter bruker den agglomerativ hierarkisk klynge på gruppeforekomster sammen. Bildet nedenfor viser utdataene du forventer, der tabellen grupperes etter Person-kolonnen .

Tabell som viser oppføringer for person som henholdsvis Miguel og Mike, og frekvens som henholdsvis 3 og 2.

Hvis du vil utføre den uklare grupperingen, utfører du de samme trinnene som tidligere er beskrevet i denne artikkelen. Den eneste forskjellen er at denne gangen merker du av for Bruk uklar gruppering i dialogboksen Grupper etter.

Avmerkingsboks for uklar gruppering i dialogboksen Grupper etter.

For hver radgruppe velger Power Query den hyppigste forekomsten som den kanoniske forekomsten. Hvis flere forekomster forekommer med samme frekvens, velger Power Query den første. Når du har valgt OK i dialogboksen Grupper etter, får du resultatet du forventet.

Fuzzy gruppering eksempel siste tabell, ingen transformering tabell.

Du har imidlertid mer kontroll over den uklare grupperingsoperasjonen ved å utvide alternativene for Fuzzy-gruppen.

Uklare gruppealternativer.

Følgende alternativer er tilgjengelige for uklar gruppering:

  • Likhetsterskel (valgfritt): Dette alternativet angir hvor lik to verdier må grupperes sammen. Minimumsinnstillingen på 0 fører til at alle verdier grupperes sammen. Den maksimale innstillingen på 1 tillater bare verdier som samsvarer nøyaktig for å grupperes sammen. Standardverdien er 0,8.
  • Ignorer store/små bokstaver: Når du sammenligner tekststrenger, ignoreres saken. Dette alternativet er aktivert som standard.
  • Grupper ved å kombinere tekstdeler: Algoritmen prøver å kombinere tekstdeler (for eksempel kombinere mikro og myk inn i Microsoft) til gruppeverdier.
  • Vis likhetsresultater: Vis likhetsresultater mellom inndataverdiene og de beregnede representative verdiene etter uklar gruppering. Krever tillegg av en operasjon, for eksempel Alle rader , for å vise denne informasjonen på rad-for-rad-nivå.
  • Transformasjonstabell (valgfritt): Du kan velge en transformasjonstabell som tilordner verdier (for eksempel tilordning av MSFT til Microsoft) for å gruppere dem sammen.

I dette eksemplet brukes en transformasjonstabell til å demonstrere hvordan verdier kan tilordnes. Transformasjonstabellen har to kolonner:

  • Fra: Tekststrengen du vil se etter i tabellen.
  • Til: Tekststrengen som skal brukes til å erstatte tekststrengen i Fra-kolonnen .

Bildet nedenfor viser transformasjonstabellen som brukes i dette eksemplet.

Tabell som viser Fra-verdiene mike og William, og Til-verdiene for Miguel og Bill.

Viktig

Det er viktig at transformasjonstabellen har de samme kolonnene og kolonnenavnene som vist ovenfor (de må være Fra og Til), ellers gjenkjenner ikke Power Query disse.

Gå tilbake til dialogboksen Grupper etter, utvid alternativer for Fuzzy-gruppen, endre operasjonen fra Antall rader til Alle rader, aktiver alternativet Vis likhetsresultater, og velg deretter rullegardinmenyen transformasjonstabell.

Rullegardinmeny for fuzzy gruppering av eksempel på transformasjonstabell.

Når du har valgt transformasjonstabellen, velger du OK. Resultatet av denne operasjonen gir deg følgende informasjon:

Fuzzy grupperingseksempel siste tabell med transformeringstabell.

I dette eksemplet ble alternativet Ignorer tilfelle aktivert, slik at verdiene i Fra-kolonnen i transformasjonstabellen brukes til å se etter tekststrengen uten å vurdere tilfellet med strengen. Denne transformasjonsoperasjonen utføres først, og deretter utføres den uklare grupperingsoperasjonen.

Likhetspoengsummen vises også i tabellverdien ved siden av personkolonnen, som gjenspeiler nøyaktig hvordan verdiene ble gruppert og deres respektive likhetsresultater. Du kan utvide denne kolonnen om nødvendig eller bruke verdiene fra de nye frekvenskolonnene for andre typer transformasjoner.

Merk

Når du grupperer etter flere kolonner, utfører transformasjonstabellen erstattoperasjonen i alle kolonner hvis det å erstatte verdien øker likhetspoengsummen.

Se også

Legge til en egendefinert kolonne
Fjerne duplikater