Gruppera eller sammanfatta rader
I Power Query kan du gruppera värden i olika rader till ett enda värde genom att gruppera raderna enligt värdena i en eller flera kolumner. Du kan välja mellan två typer av grupperingsåtgärder:
Kolumngruppering.
Radgruppering.
I den här självstudien använder du följande exempeltabell.
Skärmbild av en tabell med kolumner som visar År (2020), Land (USA, Panama eller Kanada), Produkt (Skjorta eller Shorts), Försäljningskanal (Online eller Återförsäljare) och Enheter (olika värden från 55 till 7500)
Du hittar knappen Gruppera efter på tre platser:
På fliken Start i gruppen Transformera .
På fliken Transformera i gruppen Tabell .
När du högerklickar på snabbmenyn väljer du kolumner.
I det här exemplet är målet att sammanfatta det totala antalet sålda enheter på land- och försäljningskanalnivå. Du använder kolumnerna Country och Sales Channel för att utföra gruppen efter åtgärd.
- Välj Gruppera efter på fliken Start .
- Välj alternativet Avancerat så att du kan välja flera kolumner att gruppera efter.
- Välj kolumnen Land.
- Välj Lägg till gruppering.
- Välj kolumnen Försäljningskanal.
- I Nytt kolumnnamn anger du Totalt antal enheter, i Åtgärd väljer du Summa och i Kolumn väljer du Enheter.
- Välj OK.
Den här åtgärden ger dig följande tabell.
Med funktionen Gruppera efter kan tillgängliga åtgärder kategoriseras på två sätt:
- Åtgärd på radnivå
- Åtgärd på kolumnnivå
I följande tabell beskrivs var och en av dessa åtgärder.
Åtgärdsnamn | Kategori | beskrivning |
---|---|---|
Sum | Kolumnåtgärd | Summerar alla värden från en kolumn |
Medel | Kolumnåtgärd | Beräknar medelvärdet från en kolumn |
Median | Kolumnåtgärd | Beräknar medianen från en kolumn |
Min | Kolumnåtgärd | Beräknar minimivärdet från en kolumn |
Max | Kolumnåtgärd | Beräknar det maximala värdet från en kolumn |
Percentil | Kolumnåtgärd | Beräknar percentilen med ett indatavärde från 0 till 100 från en kolumn |
Räkna distinkta värden | Kolumnåtgärd | Beräknar antalet distinkta värden från en kolumn |
Antal rader | Radåtgärd | Beräknar det totala antalet rader från en viss grupp |
Räkna distinkta rader | Radåtgärd | Beräknar antalet distinkta rader från en viss grupp |
Alla rader | Radåtgärd | Matar ut alla grupperade rader i ett tabellvärde utan sammansättningar |
Anteckning
Åtgärderna Count distinct och Percentile är endast tillgängliga i Power Query Online.
Från och med det ursprungliga exemplet skapar du i det här exemplet en kolumn som innehåller totalt antal enheter och två andra kolumner som ger dig namnet och enheterna som säljs för den mest högpresterande produkten, sammanfattade på land- och försäljningskanalnivå.
Använd följande kolumner som Gruppera efter kolumner:
- Land
- Försäljningskanal
Skapa två nya kolumner genom att följa dessa steg:
- Aggregera kolumnen Enheter med hjälp av åtgärden Summa. Namnge den här kolumnen Totalt antal enheter.
- Lägg till en ny produktkolumn med hjälp av åtgärden Alla rader.
När åtgärden är klar ser du hur kolumnen Produkter har [Tabell]-värden i varje cell. Varje [Tabell]-värde innehåller alla rader som grupperades efter kolumnerna Country och Sales Channel från den ursprungliga tabellen. Du kan välja det tomma utrymmet i cellen för att se en förhandsgranskning av innehållet i tabellen längst ned i dialogrutan.
Anteckning
Informationsförhandsgranskningsfönstret kanske inte visar alla rader som användes för grupp-by-åtgärden. Du kan välja värdet [Tabell] för att se alla rader som hör till motsvarande grupp-efter-åtgärd.
Sedan måste du extrahera raden som har det högsta värdet i kolumnen Enheter i tabellerna i den nya kolumnen Produkter och anropa den nya kolumnen Top performer product.
Med den nya kolumnen Produkter med [Tabell]-värden skapar du en ny anpassad kolumn genom att gå till fliken Lägg till kolumn i menyfliksområdet och välja Anpassad kolumn från gruppen Allmänt .
Ge den nya kolumnen namnet Top performer product ( Top performer product). Ange formeln Table.Max([Products], "Units" )
under Anpassad kolumnformel.
Resultatet av formeln skapar en ny kolumn med [Record]-värden. Dessa postvärden är i princip en tabell med bara en rad. Dessa poster innehåller raden med det maximala värdet för kolumnen Enheter för varje [tabell] värde i kolumnen Produkter.
Med den här nya produktkolumnen Top performer som innehåller [Record]-värden kan du välja expandera-ikonen, välja fälten Produkt och Enheter och sedan välja OK.
När du tar bort kolumnen Produkter och anger datatypen för båda de nyligen expanderade kolumnerna liknar resultatet följande bild.
Anteckning
Följande funktion är endast tillgänglig i Power Query Online.
Om du vill visa hur du gör "fuzzy grouping" bör du överväga exempeltabellen som visas i följande bild.
Målet med fuzzy-gruppering är att utföra en grupp-efter-åtgärd som använder en ungefärlig matchningsalgoritm för textsträngar. Power Query använder Jaccard-likhetsalgoritmen för att mäta likheten mellan instanspar. Sedan tillämpas agglomerativ hierarkisk klustring för att gruppera instanser tillsammans. Följande bild visar de utdata som du förväntar dig, där tabellen grupperas efter kolumnen Person .
Om du vill göra fuzzy-gruppering utför du samma steg som tidigare beskrivits i den här artikeln. Den enda skillnaden är att du i dialogrutan Gruppera efter den här gången markerar kryssrutan Använd fuzzy-gruppering.
För varje grupp med rader väljer Power Query den vanligaste instansen som den "kanoniska" instansen. Om flera instanser inträffar med samma frekvens väljer Power Query den första. När du har valt OK i dialogrutan Gruppera efter får du det resultat som du förväntade dig.
Du har dock mer kontroll över fuzzy-grupperingsåtgärden genom att expandera Fuzzy-gruppalternativ.
Följande alternativ är tillgängliga för fuzzy-gruppering:
- Likhetströskel (valfritt): Det här alternativet anger hur lika två värden måste vara för att grupperas tillsammans. Den minsta inställningen noll (0) gör att alla värden grupperas tillsammans. Den maximala inställningen på 1 tillåter endast att värden som matchar exakt grupperas tillsammans. Standardvärdet är 0,8.
- Ignorera skiftläge: När textsträngar jämförs ignoreras skiftläget. Det här alternativet är aktiverat som standard.
- Gruppera genom att kombinera textdelar: Algoritmen försöker kombinera textdelar (till exempel att kombinera Micro och mjuk till Microsoft) för att gruppera värden.
- Visa likhetspoäng: Visa likhetspoäng mellan indatavärdena och de beräknade representativa värdena efter fuzzy-gruppering. Kräver att en åtgärd läggs till, till exempel Alla rader , för att visa den här informationen på rad-för-rad-nivå.
- Transformeringstabell (valfritt): Du kan välja en transformeringstabell som mappar värden (till exempel mappning av MSFT till Microsoft) för att gruppera dem.
I det här exemplet används en transformeringstabell för att visa hur värden kan mappas. Transformeringstabellen har två kolumner:
- Från: Textsträngen som du vill söka efter i tabellen.
- Till: Textsträngen som ska användas för att ersätta textsträngen i kolumnen Från .
Följande bild visar transformeringstabellen som används i det här exemplet.
Viktigt
Det är viktigt att transformeringstabellen har samma kolumner och kolumnnamn som i föregående bild (de måste vara märkta "Från" och "Till"). Annars känner Power Query inte igen tabellen som en transformeringstabell.
Gå tillbaka till dialogrutan Gruppera efter, expandera Fuzzy-gruppalternativ, ändra åtgärden från Antal rader till Alla rader, aktivera alternativet Visa likhetspoäng och välj sedan listrutan Transformeringstabell .
När du har valt transformeringstabellen väljer du OK. Resultatet av åtgärden ger dig följande information:
I det här exemplet aktiverades alternativet Ignorera skiftläge, så värdena i kolumnen Från i tabellen Transformering används för att leta efter textsträngen utan att ta hänsyn till strängens skiftläge. Den här transformeringsåtgärden inträffar först och sedan utförs den fuzzy-grupperingsåtgärden.
Likhetspoängen visas också i tabellvärdet bredvid personkolumnen, vilket återspeglar exakt hur värdena grupperades och deras respektive likhetspoäng. Du kan expandera den här kolumnen om det behövs eller använda värdena från de nya frekvenskolumnerna för andra typer av transformeringar.
Anteckning
När du grupperar efter flera kolumner utför transformeringstabellen ersättningsåtgärden i alla kolumner om ersättningen av värdet ökar likhetspoängen.
Mer information om hur transformeringstabeller fungerar finns i Transformeringstabellprecept.