Normalisera kolumner
I Power Query kan du omvandla kolumner till attribut/värde-par, där kolumner blir rader.
Diagram som visar den vänstra tabellen med en tom kolumn och rader och attributvärdena A1, A2 och A3 som kolumnrubriker. I den här tabellen innehåller kolumnen A1 värdena V1, V4 och V7. Kolumnen A2 innehåller värdena V2, V5 och V8. Kolumnen A3 innehåller värdena V3, V6 och V9. Med kolumnerna obevakade innehåller den högra tabellen i diagrammet en tom kolumn och rader, en attributkolumn med nio rader med A1, A2 och A3 upprepade tre gånger och en värdekolumn med värdena V1 till V9.
Med en tabell som följande, där landsrader och datumkolumner skapar en matris med värden, är det till exempel svårt att analysera data på ett skalbart sätt.
Skärmbild av en tabell som innehåller en country-kolumn som angetts i datatypen Text och tre kolumner med datumen 1 juni 2023, 1 juli 2023 och 1 augusti 2023 angivna som datatypen Heltal. Kolumnen Land innehåller USA på rad 1, Kanada på rad 2 och Panama på rad 3.
I stället kan du omvandla tabellen till en tabell med kolumner som inte har sparats, som du ser i följande bild. I den transformerade tabellen är det enklare att använda datumet som ett attribut att filtrera på.
Skärmbild av tabellen som innehåller en country-kolumnuppsättning som datatypen Text, en attributkolumn som angetts som datatypen Text och en värdekolumn som angetts som datatypen Heltal. Kolumnen Land innehåller USA i de tre första raderna, Kanada i de följande tre raderna och Panama i de tre sista raderna. Kolumnen Attribut innehåller datumet 1 juni 2023 i den första, fjärde och sjunde raden, datumet den 1 juli 2023 i den andra, femte och åttonde raden och datumet den 1 augusti 2023 på den tredje, sjätte och nionde raden.
Nyckeln i den här omvandlingen är att du har en uppsättning datum i tabellen som alla ska ingå i en enda kolumn. Respektive värde för varje datum och land bör finnas i en annan kolumn, vilket effektivt skapar ett attribut/värde-par.
Power Query skapar alltid attribute-value-paret med hjälp av två kolumner:
- Attribut: Namnet på de kolumnrubriker som inte har markerats.
- Värde: De värden som fanns under var och en av de obevakade kolumnrubrikerna.
Det finns flera platser i användargränssnittet där du hittar Unpivot-kolumner. Du kan högerklicka på de kolumner som du vill inaktivera eller välja kommandot från fliken Transformera i menyfliksområdet.
Det finns tre sätt att avpivota kolumner från en tabell:
- Avpivot-kolumner
- Ta bort andra kolumner
- Avmarkera endast markerade kolumner
För det tidigare beskrivna scenariot måste du först välja de kolumner som du vill avpivot. Du kan välja Ctrl när du väljer så många kolumner som du behöver. I det här scenariot vill du markera alla kolumner utom den med namnet Land. När du har valt kolumnerna högerklickar du på någon av de markerade kolumnerna och väljer sedan Ta bort begärandekolumner.
Skärmbild av tabellen med kolumnerna 1 juni 2023, 1 juli 2023 och 1 augusti 2023 markerade och kommandot Unpivot columns valt i snabbmenyn."
Resultatet av åtgärden ger resultatet som visas i följande bild.
När du har skapat frågan från föregående steg kan du tänka dig att den första tabellen uppdateras så att den ser ut som följande skärmbild.
Skärmbild av tabellen med samma ursprungliga datumkolumner för land, 1 juni 2023, 1 juli 2023 och augusti 2023, med en datumkolumn den 1 september 2023. Kolumnen Land innehåller fortfarande värdena USA, Kanada och Panama, men storbritannien har också lagts till på den fjärde raden och Mexiko läggs till på den femte raden.
Observera att du lägger till en ny kolumn för datumet 1 september 2023 (2023-09-1) och två nya rader för länderna/regionerna Storbritannien och Mexiko.
Om du uppdaterar frågan ser du att åtgärden utförs i den uppdaterade kolumnen, men påverkar inte den kolumn som inte ursprungligen valdes (Land, i det här exemplet). Det innebär att alla nya kolumner som du har lagt till i källtabellen också är opivoterade.
Följande bild visar hur frågan ser ut efter uppdateringen med den nya uppdaterade källtabellen.
Skärmbild av tabellen med kolumnerna Land, Attribut och Värde. De första fyra raderna i kolumnen Land innehåller USA, de andra fyra raderna innehåller Kanada, de tredje fyra raderna innehåller Panama, de fjärde fyra raderna innehåller Storbritannien och de femte fyra raderna innehåller Mexiko. Kolumnen Attribut innehåller datumen 1 juni 2023, 1 juli 2023 och augusti 2023 på de fyra första raderna, som upprepas för varje land.
Du kan också välja de kolumner som du inte vill avpivot och avpivot resten av kolumnerna i tabellen. Den här åtgärden är den där Unpivot andra kolumner spelar in.
Resultatet av den åtgärden ger exakt samma resultat som det du fick från Unpivot-kolumner.
Skärmbild av tabellen som innehåller en country-kolumnuppsättning som datatypen Text, en attributkolumn som angetts som datatypen Text och en värdekolumn som angetts som datatypen Heltal. Kolumnen Land innehåller USA i de tre första raderna, Kanada i de följande tre raderna och Panama i de tre sista raderna. Kolumnen Attribut innehåller datumet 1 juni 2023 i den första, fjärde och sjunde raden, datumet den 1 juli 2023 i den andra, femte och åttonde raden och datumet den 1 augusti 2023 på den tredje, sjätte och nionde raden.
Anteckning
Den här omvandlingen är avgörande för frågor som har ett okänt antal kolumner. Åtgärden avregistrerar alla kolumner från tabellen förutom de som du har valt. Det här är en idealisk lösning om datakällan i ditt scenario fick nya datumkolumner i en uppdatering, eftersom dessa hämtas och inaktiveras.
Om frågan uppdateras och mer data hämtas från datakällan på liknande sätt som åtgärden Unpivot-kolumner, tas alla kolumner bort utom de som tidigare har valts.
För att illustrera den här processen kan du säga att du har en ny tabell som den i följande bild.
Skärmbild av tabellen med kolumnerna Country, 1 juni 2023, 1 juli 2023, 1 augusti 2023 och 1 september 2023, med alla kolumner inställda på datatypen Text. Kolumnen Land innehåller, uppifrån och ned, USA, Kanada, Panama, Storbritannien och Mexiko.
Du kan välja kolumnen Land och sedan välja Ta bort andra kolumner, vilket ger följande resultat.
Skärmbild av tabellen med kolumnerna Land, Attribut och Värde. Kolumnerna Land och Attribut är inställda på datatypen Text. Kolumnen Värde är inställd på datatypen Helt värde. De första fyra raderna i kolumnen Land innehåller USA, de andra fyra raderna innehåller Kanada, de tredje fyra raderna innehåller Panama, de fjärde fyra raderna innehåller Storbritannien och de femte fyra raderna innehåller Mexiko. Kolumnen Attribut innehåller 1 juni 2023, 1 juli 2023, 1 augusti 2023 och 1 september 2023 på de fyra första raderna, som upprepas för varje land.
Syftet med det sista alternativet är att endast ta bort specifika kolumner från tabellen. Det här alternativet är viktigt för scenarier där du hanterar ett okänt antal kolumner från datakällan och du bara vill ta bort de markerade kolumnerna.
Om du vill utföra den här åtgärden väljer du de kolumner som ska avpivot, vilket i det här exemplet är alla kolumner utom kolumnen Land . Högerklicka sedan på någon av de kolumner som du har valt och välj sedan Endast markerade kolumner.
Observera hur den här åtgärden ger samma utdata som i föregående exempel.
Skärmbild av tabellen som innehåller en country-kolumnuppsättning som datatypen Text, en attributkolumn som angetts som datatypen Text och en värdekolumn som angetts som datatypen Heltal. Kolumnen Land innehåller USA i de tre första raderna, Kanada i de följande tre raderna och Panama i de tre sista raderna. Kolumnen Attribut innehåller datumet 1 juni 2023 i de första, fjärde och sjunde raderna, datumet den 1 juli 2023 i den andra, femte och åttonde raden och 1 augusti 2023 på den tredje, sjätte och nionde raden.
Om källtabellen ändras till att ha en ny 9/1/2020-kolumn och nya rader för Storbritannien och Mexiko, skiljer sig källtabellen från föregående exempel. Anta att källtabellen efter en uppdatering ändras till tabellen i följande bild.
Utdata från vår fråga ser ut som följande bild.
Det ser ut så här eftersom åtgärden unpivot endast tillämpades på kolumnerna 6/1/2020, 7/1/2020 och 8/1/2020 , så kolumnen med rubriken 9/1/2020 förblir oförändrad.