Dimensietabel in een tekstbestand: Power Query-oplossing

In deze blogpost ga ik in op probleem #2 van het doorlopende Probleem van de Week op het , waar we bespraken hoe rommelige gegevens in een schone dimensietabel kunnen worden gesorteerd. Dit is de tweede iteratie van dit nieuwe initiatief dat we hosten bij LuckyTemplates. Ik ben vooral gepassioneerd door deze serie omdat het iedereen de kans geeft om regelmatig meer te oefenen. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Elke eerste woensdag van de maand is er een DAX-uitdaging en op de derde woensdag een Power Query-uitdaging .

Dit is een geweldige kans om nieuwe dingen te verkennen, te ontdekken en te leren over deze talen die je nodig hebt om binnen LuckyTemplates te benutten.

Je vindt een categorie over het probleem van de week op het LuckyTemplates-forum.

De primaire focus ligt op het proces en niet zozeer op het resultaat.

Als je erin slaagt een probleem in kleinere stukjes op te splitsen, kun je elk probleem waarmee je wordt geconfronteerd oplossen.

Melissa de Korte

Brian sprak eerder over een techniek genaamd rubber duck debugging . Als je dat gemist hebt, bekijk dan zeker zijn video. Het kan je helpen als je vastloopt op deze uitdagingen.

Laten we eerst eens kijken naar de taak die voorhanden is. We hebben een rommelig tekstbestand dat we moeten omzetten in een goede dimensietabel.

Als ik nu naar dit soort gegevens kijk, gaat het voor het grootste deel allemaal om het opschonen van tekst, het verwijderen van ongewenste tekens, het bijsnijden, het gebruik van hoofdletters, enzovoort.

Maar we moeten ook alle rijen uit deze stapelgegevens ophalen en deze voor elk land in één regel veranderen. Ik noem het stapelgegevens omdat alle veldnamen voor elk land in één enkele kolom worden herhaald.

Ik heb enige ervaring met Excel, en een van de krachtigste functies ervan is naar mijn mening draaitabellen .

Inhoudsopgave

• Draaitabellen in Excel
• Power Query gebruiken om dimensietabel te maken
• Aangepaste tekstreinigingsfunctie
• Draaien van de gegevens
• Conclusie

Draaitabellen in Excel

Met draaitabellen kan ik gegevens segment voor segment bekijken. Afhankelijk van wat u op de rijsectie plaatst, condenseert een draaitabel elk voorkomen van dat segment in een enkele regel.

U kunt dat vervolgens nog verder opsplitsen door velden naar het kolomgedeelte te slepen.

Ik heb een voorbeeld gemaakt van hoe dit eruit ziet. Hier hebben we vrijwel dezelfde gegevens die we in het tekstbestand hadden.

En op de volgende pagina heb ik een lege draaitabel gemaakt.

Wat ik je niet heb laten zien, is dat ik hier drie kolommen heb in plaats van de twee die ik je al heb laten zien.

Ik ga mijn segment naar de rijen slepen.

Omdat kolom 1 de veldnamen had, sleep ik die naar de kolomsectie.

Kolom 2 had alle veldwaarden, dus die ga ik naar het waardegedeelte slepen.

We kunnen hier zien dat de draaitabellen geen tekenreeksen aankunnen. Het telt ze, maar het laat wel zien dat we een enkele waarde hebben voor elk veld.

Laten we nu eens kijken naar dat segment dat ik heb gemaakt. Dus ik ga terug naar de gegevens en ik ga mijn kolom zichtbaar maken. U kunt zien dat het slechts een indexnummer is, dat elk van de afzonderlijke blokken van de gegevens identificeert die nog op elkaar zijn gestapeld.

Dus voor mij zal de sleuteltransformatie een spil zijn van de gegevens om ze terug te krijgen in dat tabelformaat.

Op het forum heb ik andere manieren gezien om hiermee om te gaan. Er zijn andere manieren om naar een tabelindeling te gaan, afgezien van het gebruik van een spilactie, en ze werken even goed. Als je daarin geïnteresseerd bent, ga dan naar het forum en begin met verkennen.

Power Query gebruiken om dimensietabel te maken

Laten we de vermogensvraag doornemen en mijn oplossing bekijken.

Persoonlijk denk ik dat de gebruikersinterface het grootste deel van de M-code uitstekend voor ons schrijft. Dus ik heb de neiging om mijn vragen zo veel mogelijk te ontwerpen met behulp van de gebruikersinterface.

Zodra de query doet wat ik nodig had, ga ik naar de geavanceerde editor en onderzoek ik de M-code om te zien of ik deze kan wijzigen. Laten we eens kijken hoe dat eruit ziet.

Dit is mijn basisgroep die is gebouwd met behulp van de gebruikersinterface.

U kunt zien dat er veel stappen zijn in de toegepaste stappen aan de rechterkant.

Nu is dat op zich geen probleem, maar alleen al door naar deze stappen te kijken, zie je dat er veel transformaties zijn die kunnen worden gegroepeerd.

Laten we de geavanceerde editor openen.

We zien dat deze query 31 stappen heeft.

Ik heb ook enkele opmerkingen toegevoegd aan dezelfde query met de 31 stappen, maar ik heb deze opgesplitst in secties.

Het eerste dat ik deed, was de stap voor het wijzigingstype verwijderen. Ik stel voor een aangepaste functie te maken om al deze stappen voor teksttransformatie uit te voeren.

Er zijn hier veel reacties, maar er zijn slechts twee dingen die ik in deze blogpost wil benadrukken. Ten eerste is er de aangepaste functie voor het opschonen van tekst .

De tweede stap is de spilstap om dit soort gegevens weer om te zetten in een correct tabelformaat .

Aangepaste tekstreinigingsfunctie

Laten we teruggaan naar de vroege stadia van het uitbouwen van mijn query, waar ik al die gegroepeerde stappen had om deze teksten op te schonen: kolom 1 en de samengevoegde kolom.

Ik heb ook een extra aangepaste kolom toegevoegd. Het enige doel is om mijn aangepaste functie voor het opschonen van tekst uit te bouwen . Ik heb dat aangeroepen op de samengevoegde kolom.

Op deze manier hoef ik de functie niet in één keer te schrijven, maar bouw ik hem stap voor stap op, waarbij ik een nieuwe transformatie toevoeg na het bekijken van de resultaten van de vorige stap.

Laten we eens kijken naar de M-code voor de functie voor het opschonen van tekst.

Zoals je kunt zien, heb ik meerdere stappen. Toen ik deze tekstfunctie aan het bouwen was, ging ik heen en weer tussen de query om naar de resultaten te kijken om te zien wat ik moest bouwen en wat ik vervolgens moest corrigeren.

Met dit resultaat heb ik alle transformaties uitgevoerd die ik nodig had. Sommige van de M-functies die ik hier gebruikte, werden geleverd door de gebruikersinterface toen ik mijn eerste query bouwde, zoals de tekst. Trim-functie. De andere gebruikte functies waren dat echter niet.

Als u ze niet kent, kunt u alle M-functies online opzoeken in de M-formulegids. Dit is de link waar je heen moet.

U vindt een sectie gewijd aan power query en functies.

Als u naar beneden scrolt, vindt u het gedeelte over tekstfunctie en elk onderdeel begint met een overzicht. Er is een lijst met alle tekstfuncties in de Power Query en formule M-taal.

Als u op zoek bent naar een specifieke transformatie, kunt u die hier opzoeken.

Draaien van de gegevens

Het tweede deel dat ik wilde benadrukken bij het maken van deze dimensietabel, is het draaien van de gegevens zelf. Laten we dit ook eens nader bekijken.

Ik begon met het toevoegen van een index. Ik heb die index bijgewerkt om de gegevensblokken correct te segmenteren. Ik deed dit door het indexnummer terug te geven voor elke regel waar het tekstland in kolom 1 stond, en vervolgens vulde ik die waarde in.

Het enige dat we hoeven te doen, is de gegevens zelf draaien. Op het tabblad Transformeren vindt u de draaikolom. Selecteer Kolom 1 en klik op Draaikolom.

Het gaat de waarden van kolom 1 gebruiken als de nieuwe kolomnaam. Het wil ook weten waar de waarden voor die veldnamen zijn. Die staan ​​in onze samengevoegde kolom.

Als Excel met tekstwaarden kan omgaan, kan Power Query dat ook vanwege de instelling van geavanceerde opties. Het enige wat we hoeven te doen is Don't Aggregate selecteren om tekstwaarden te kunnen verwerken.

Zodra we op OK klikken, kunnen we zien dat onze gegevens zijn gedraaid.

Conclusie

De afbeelding hierboven is de uiteindelijke resultaatquery. Ik hoop dat je genoten hebt van de manier waarop we de rommelige gegevens in het meegeleverde tekstbestand hebben omgevormd tot een overzichtelijke dimensietabel die geschikt is voor analyse.

Als je deze blogpost leuk vond, abonneer je dan op het LuckyTemplates-kanaal zodat je geen nieuwe inhoud mist.

Ik hoop jullie allemaal te zien in toekomstige Problem of the Week-uitdagingen.

Melissa