Een van de dingen die ik het meest vermeld over PowerPivot voor Excel, is de mogelijkheid om opzoektabellen aan uw datasets toe te voegen. Meestal hebben de gegevens waarmee u werkt niet elk veld dat u voor uw analyse nodig hebt. U kunt bijvoorbeeld een datumveld hebben, maar uw gegevens moeten per kwartaal worden gegroepeerd. U kunt een formule schrijven, maar het is eenvoudiger om een eenvoudige opzoektabel te maken binnen de PowerPivot-omgeving.
U kunt deze opzoektabel ook gebruiken voor een andere groepering, zoals de naam van de maand en de eerste / tweede helft van het jaar. In termen van datawarehousing maakt u eigenlijk een datumdimensietabel. In dit artikel geef ik u een aantal voorbeeldtafeltabellen om uw PowerPivot voor Excel-project te verbeteren.
Nieuwe Text Dimension (Lookup) -tabel
Laten we een tabel met ordergegevens overwegen. Stel dat de tabel velden bevat voor klant, besteldatum, ordertotaal en ordertype. We gaan ons richten op het veld met het ordertype. Stel dat het veld voor het ordertype waarden bevat zoals:
- netbooks
- Desktops
- monitors
- projectoren
- printers
- Scanners
- Digitale fototoestellen
- Digitale SLR-camera's
- Filmcamera's
- Camcorders
- Office-telefoons
- Smartphones
- PDA's
- Mobiele telefoons Accessoires
In werkelijkheid zou je hier codes voor hebben, maar om dit voorbeeld simpel te houden, neem aan dat dit de werkelijke waarden in de tabel zijn. Met PowerPivot voor Excel kunt u eenvoudig uw orders groeperen op ordertype.
Wat als je een andere groep wilde? Stel bijvoorbeeld dat u een categoriegroep zoals computers, camera's en telefoons nodig hebt. De ordertabel heeft geen categorieveld, maar u kunt het eenvoudig maken als een opzoektabel in PowerPivot voor Excel. De volledige voorbeeldopzoektabel staat hieronder tafel 1 .
Dit zijn de stappen:
- Stap 1: U hebt een aparte lijst uit het typeveld nodig voor uw opzoektabel. Dit wordt uw opzoekveld. Maak vanuit uw gegevensset een afzonderlijke lijst met waarden in het veld Ordertype. Voer de aparte lijst met typen in een Excel-werkmap in. Label de kolom Type .
- Stap 2: Voeg in de kolom naast uw opzoekkolom (Type) het nieuwe veld toe waarnaar u wilt groeperen. Voeg in ons voorbeeld een kolom toe met een label met de naam Categorie .
- Stap 3: Voeg de bijbehorende toe voor elke waarde in uw afzonderlijke lijst met waarden (typen in dit voorbeeld) Categorie waarden. Voer in ons eenvoudige voorbeeld een van beide in computers , camera's of phones in de Categorie kolom.
- Stap 4: Kopieer de Type en Categorie datatabel op uw klembord.
- Stap 5: Open de Excel-werkmap met de ordergegevens in PowerPivot voor Excel. Start het PowerPivot-venster. Klik op Plakken, wat uw nieuwe opzoektabel oplevert. Geef de tafel een naam en zorg ervoor dat je dit controleert Gebruik de eerste rij als kolomkoppen. Klik OK. U hebt een opzoektabel gemaakt in PowerPivot.
- Stap 6: Creëer een relatie tussen de Type veld in de Bestellen tafel en de Categorie veld in de opzoektabel. Klik op de Ontwerp lint en kies Maak relatie. Maak de selecties in de Maak relatie dialoogvenster en klik creëren.
Wanneer u een draaitabel in Excel maakt op basis van de PowerPivot-gegevens, kunt u groeperen op basis van uw nieuwe Categorie veld. Houd er rekening mee dat PowerPivot voor Excel alleen interne joins ondersteunt. Als je een hebt order type ontbreekt in uw opzoektabel, zullen alle bijbehorende records voor dat type ontbreken in elke draaitabel op basis van de PowerPivot-gegevens. U moet dit van tijd tot tijd controleren.
Date Dimension (Lookup) -tabel
De opzoektabel voor de datum is waarschijnlijk nodig in de meeste van uw PowerPivot voor Excel-projecten. De meeste gegevenssets hebben een of meer datumveld (en). Er zijn functies om het jaar en de maand te berekenen.
Als u echter de werkelijke maandtekst of het kwartaal nodig heeft, moet u een complexe formule schrijven. Het is veel eenvoudiger om een tabel met datumdimensies (opzoeken) op te nemen en deze te laten overeenkomen met het maandnummer in uw hoofdgegevensset. U moet een kolom aan uw ordertabel toevoegen om het maandnummer uit het veld Orderdatum weer te geven. De DAX-formule voor maand in ons voorbeeld is dat = MAAND (Besteldatum). Hierdoor wordt voor elke record een getal tussen 1 en 12 weergegeven. Onze dimensietabel biedt alternatieve waarden die verwijzen naar het maandnummer. Dit biedt u flexibiliteit in uw analyse. De volledige bematingsdatummeductietabel staat hieronder tafel 2 .
De datumdimensie of opzoektabel bevat 12 records. De kolom voor de maand heeft de waarden 1 - 12. Andere kolommen bevatten afgekorte maandtekst, volledige maandtekst, kwartaal, enzovoort. Dit zijn de stappen:
- Stap 1: Kopieer de tabel uit onderstaande tabel 2 en plak deze in PowerPivot. U zou deze tabel in Excel kunnen maken, maar ik bespaar u tijd. U zou in staat moeten zijn om rechtstreeks van de geselecteerde gegevens hieronder te plakken als u Internet Explorer of Edge gebruikt. PowerPivot neemt de tabelopmaak op in mijn tests. Als u een andere browser gebruikt, moet u deze mogelijk eerst in Excel plakken en vanuit Excel kopiëren om de tabelopmaak op te halen.
- Stap 2: Open de Excel-werkmap met de ordergegevens in PowerPivot voor Excel. Start het PowerPivot-venster. Klik Pasta die uw opzoektabel zal ophalen gekopieerd uit de onderstaande tabel of uit Excel. Geef de tafel een naam en zorg ervoor dat je dit controleert Gebruik de eerste rij als kolomkoppen. Klik OK. U hebt een datumopzoektabel gemaakt in PowerPivot.
- Stap 3: Maak een relatie tussen de Maand veld in de Bestellen tafel en de MonthNumber veld in de opzoektabel. Klik op de Ontwerp lint en kies Maak relatie. Maak de selecties in de Maak relatie dialoogvenster en klik creëren.
Nogmaals, met de toevoeging van een datumdimensie kunt u de gegevens in uw draaitabel groeperen met behulp van een van de verschillende waarden uit de datumopzoektabel. Groeperen per kwartaal of de naam van de maand zal in een handomdraai zijn.
Sample Dimension (Lookup) -tabellen
tafel 1
| Type | Categorie |
| netbooks | Computer |
| Desktops | Computer |
| monitors | Computer |
| Projectoren & schermen | Computer |
| Printers, scanners en faxen | Computer |
| Computer Setup & Service | Computer |
| Computers accessoires | Computer |
| Digitale fototoestellen | Camera |
| Digitale SLR-camera's | Camera |
| Filmcamera's | Camera |
| Camcorders | Camera |
| Accessoires voor camera's en camcorders | Camera |
| Thuis- en kantoortelefoons | Telefoon |
| Telefoontoestellen met aanraakscherm | Telefoon |
| Smartphones & PDA's | Telefoon |
tafel 2
| MonthNumber | MonthTextShort | MonthTextFull | Kwartaal | Semester |
| 1 | jan | januari- | Q1 | H1 |
| 2 | februari | februari | Q1 | H1 |
| 3 | bederven | maart | Q1 | H1 |
| 4 | april | april | Q2 | H1 |
| 5 | mei | mei | Q2 | H1 |
| 6 | juni | juni- | Q2 | H1 |
| 7 | juli | juli- | Q3 | H2 |
| 8 | augustus | augustus | Q3 | H2 |
| 9 | september | september | Q3 | H2 |
| 10 | oktober | oktober | Q4 | H2 |
| 11 | november | november | Q4 | H2 |
| 12 | december | december | Q4 | H2 |
