Inhoud
Formulering van het probleem
Als invoergegevens hebben we een Excel-bestand, waarbij een van de bladen meerdere tabellen bevat met verkoopgegevens van de volgende vorm:
Merk op dat:
- Tabellen van verschillende afmetingen en met verschillende sets producten en regio's in rijen en kolommen zonder enige sortering.
- Tussen tabellen kunnen blanco regels worden ingevoegd.
- Het aantal tabellen kan willekeurig zijn.
Twee belangrijke aannames. Er wordt aangenomen dat:
- Boven elke tabel, in de eerste kolom, staat de naam van de manager wiens verkoop de tabel illustreert (Ivanov, Petrov, Sidorov, enz.)
- De namen van goederen en regio's in alle tabellen zijn op dezelfde manier geschreven - met een nauwkeurigheid van hoofdletters.
Het uiteindelijke doel is om gegevens van alle tabellen te verzamelen in één platte genormaliseerde tabel, handig voor latere analyse en het maken van een samenvatting, bijvoorbeeld in deze:
Stap 1. Maak verbinding met het bestand
Laten we een nieuw leeg Excel-bestand maken en dit op het tabblad selecteren Data commando Gegevens ophalen – Uit bestand – Uit boek (Gegevens — Uit bestand — Uit werkmap). Specificeer de locatie van het bronbestand met verkoopgegevens en selecteer vervolgens in het navigatorvenster het blad dat we nodig hebben en klik op de knop Gegevens converteren (Gegevens transformeren):
Als gevolg hiervan moeten alle gegevens ervan in de Power Query-editor worden geladen:
Stap 2. Ruim de prullenbak op
Automatisch gegenereerde stappen verwijderen gewijzigd type: (Gewijzigd type) и Verhoogde koppen (Gepromote kopteksten) en verwijder lege regels en regels met totalen met behulp van een filter nul и TOTAAL door de eerste kolom. Als resultaat krijgen we het volgende beeld:
Stap 3. Beheerders toevoegen
Om later te begrijpen waar wiens verkopen zijn, is het noodzakelijk om een kolom aan onze tabel toe te voegen, waar in elke rij een overeenkomstige achternaam zal zijn. Voor deze:
1. Laten we een hulpkolom met regelnummers toevoegen met het commando Kolom toevoegen – Indexkolom – Van 0 (Kolom toevoegen — Indexkolom — Van 0).
2. Voeg een kolom toe met een formule met het commando Een kolom toevoegen – Aangepaste kolom (Kolom toevoegen — Aangepaste kolom) en voer daar de volgende constructie in:
De logica van deze formule is eenvoudig - als de waarde van de volgende cel in de eerste kolom "Product" is, betekent dit dat we het begin van een nieuwe tabel zijn tegengekomen, dus we geven de waarde van de vorige cel weer met de naam van de beheerder. Anders geven we niets weer, dwz null.
Om de bovenliggende cel met de achternaam te krijgen, verwijzen we eerst naar de tabel uit de vorige stap #”Index toegevoegd”en geef vervolgens de naam op van de kolom die we nodig hebben [Kolom 1] tussen vierkante haken en het celnummer in die kolom tussen accolades. Het celnummer is één minder dan het huidige nummer, dat we uit de kolom halen Index, Respectievelijk.
3. Het blijft om de lege cellen in te vullen met nul namen uit hogere cellen met het commando Transformeren – Vullen – Omlaag (Transformeren — Vullen — Omlaag) en verwijder de niet langer benodigde kolom met indexen en rijen met achternaam in de eerste kolom. Als resultaat krijgen we:
Stap 4. Groeperen in aparte tabellen door managers
De volgende stap is om de rijen voor elke manager in afzonderlijke tabellen te groeperen. Gebruik hiervoor op het tabblad Transformatie het commando Groeperen op (Transformeren – Groeperen op) en selecteer in het geopende venster de kolom Manager en de bewerking Alle rijen (Alle rijen) om eenvoudig gegevens te verzamelen zonder een aggregatiefunctie toe te passen op ze (som, gemiddelde, enz.). P.):
Hierdoor krijgen we voor elke beheerder aparte tabellen:
Stap 5: Transformeer geneste tabellen
Nu geven we de tabellen die in elke cel van de resulterende kolom liggen Alle gegevens in behoorlijke staat.
Verwijder eerst een kolom die niet langer nodig is in elke tabel Manager. We gebruiken weer Aangepaste kolom tab Transformatie (Transformeren — Aangepaste kolom) en de volgende formule:
Vervolgens verhogen we met een andere berekende kolom de eerste rij in elke tabel naar de kopjes:
En tot slot voeren we de hoofdtransformatie uit - elke tafel uitvouwen met behulp van de M-functie Table.UnpivotOtherColumns:
De namen van de regio's uit de kop gaan naar een nieuwe kolom en we krijgen een smallere, maar tegelijkertijd langere genormaliseerde tabel. Lege cellen met nul worden genegeerd.
Het wegwerken van onnodige tussenliggende kolommen, we hebben:
Stap 6 Geneste tabellen uitvouwen
Het blijft over om alle genormaliseerde geneste tabellen uit te breiden tot een enkele lijst met behulp van de knop met dubbele pijlen in de kolomkop:
… en we krijgen eindelijk wat we wilden:
U kunt de resulterende tabel terug exporteren naar Excel met behulp van de opdracht Home — Sluiten en laden — Sluiten en laden in… (Home — Sluiten&Laden — Sluiten&Laden naar…).
- Maak tabellen met verschillende koppen uit meerdere boeken
- Gegevens verzamelen van alle bestanden in een bepaalde map
- Gegevens van alle bladen van het boek in één tabel verzamelen