Power Query Modulo: omgaan met gestapelde gegevens

In deze zelfstudie laat ik zien hoe u rommelige gestapelde gegevens kunt omzetten in een meer georganiseerd formaat. Er is een vrij gemakkelijke manier om dit te doen, en het is er een die ongelooflijk nuttig zal zijn en veel verschillende toepassingen heeft. We gaan de Power Query-modulo gebruiken. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Mijn vrouw kwam het kantoor binnen en zei dat ze een aantal adressen uit een agenda-uitnodiging had gekopieerd en dat het uiteindelijk in dit formaat was geplakt. Ze had 28 e-mailadressen en namen die in dit soort rare, gestapelde indeling terechtkwamen. Het was een lijst met namen met een heleboel aanhalingstekens, komma's, junkmail en e-mailadressen. Ze wilde weten hoe ze Power Query kon gebruiken om dat in een betere indeling te krijgen.

Laten we dus naar Power Query springen en de onbewerkte gegevens bekijken. Laten we de onbewerkte gegevens intact houden en er gewoon naar verwijzen. We noemen dit referentiebestand TestData .

Het eerste dat we gaan doen, is een index toevoegen en het maakt voor deze doeleinden niet echt uit of het een op nul gebaseerde index of een op één gebaseerde index is. We gebruiken alleen een op nul gebaseerde index voor deze demonstratie. Het is gemakkelijk om te klikken zonder andere opties te hoeven doorlopen.

Dit gaat een spil inhouden omdat een spil rijen neemt en ze in kolommen verandert. In dit geval willen we deze reeks rijen nemen en er drie kolommen van maken: één voor de voornaam, één voor de achternaam en één voor het e-mailadres.

We gaan die in de kolommen laten draaien, maar we moeten een manier hebben waarop de spil weet welk element in welke kolom komt. Dit is waar Power Query Modulo om de hoek komt kijken.

Inhoudsopgave

• Omgaan met gestapelde gegevens met behulp van Power Query Modulo
• Conclusie

Omgaan met gestapelde gegevens met behulp van Power Query Modulo

Modulo geeft ons de rest als we een bepaald getal delen. Om toegang te krijgen tot Modulo, gaan we naar het tabblad Kolom toevoegen, dan zoeken we Standaard, klikken op de vervolgkeuzepijl en scrollen naar beneden.

Wanneer we op Modulo klikken, krijgen we een pop-upvenster waarin wordt gevraagd naar het aantal kolommen dat we willen. In dit geval willen we drie kolommen.

Als we drie door drie delen, krijgen we één met een rest van nul. Drie in vier geeft een rest van één, drie in vijf geeft een rest van twee, en drie in zes geeft ons een rest van nul. Dus het produceert dit mooie herhalende patroon dat we kunnen gebruiken voor onze spil.

Maar voordat we dat doen, laten we de gegevens opschonen. Er zijn twee manieren waarop we dit kunnen doen. Een daarvan is dat we eenvoudig waarden kunnen vervangen. Voor de aanhalingstekens kunnen we bijvoorbeeld de aanhalingstekens erin plaatsen en dan vervangen door niets. Het zal die aanhalingstekens verwijderen en opschonen.

De andere manier om dit te doen, waarbij we het allemaal tegelijk kunnen opschonen en enkele stappen kunnen opslaan, is door een functie genaamd Text.Remove te gebruiken . Eerst voegen we een aangepaste kolom toe en vervolgens vermelden we alle waarden die we willen verwijderen.

Zoals je kunt zien, ruimt dat alles mooi en gemakkelijk op. En dus kunnen we nu die eerste kolom verwijderen en die aangepaste kolom verplaatsen.

We kunnen nu onze spil netjes doen. We nemen het op tegen de Modulo, gaan naar Transformeren en dan Pivot Column, en we gaan draaien op de Custom, maar we willen niet dat het aggregeert. We kiezen dus voor Niet aggregeren en klikken op OK.

We vervangen het gewoon door deze waarden in de kolom. Vervolgens worden die drie kolommen voor ons gemaakt. We kunnen de achternaam, voornaam en e-mailadres daarin zien, maar het is niet precies het formaat dat we willen.

Om dat op te lossen, kunnen we deze kolommen nemen en vervolgens een Fill Up doen .

En nu hebben we deze extra rijen vanwege het herhalende patroon van de Modulo.

We kunnen daar gemakkelijk vanaf komen door alternatieve rijen te verwijderen. We kunnen dat doen in de optie Rijen verwijderen.

We kunnen zeggen dat de eerste te verwijderen rij twee is. Verwijder vervolgens de tweede en derde rij, en houd dan de vierde en doe dat als een herhalend patroon.

U kunt nu zien dat we alle juiste gegevens op een mooie manier hebben opgeschoond en dat we nog steeds de 28 originele e-mailadressen hebben.

Nu kunnen we de kolom Index verwijderen en de kolomkoppen dienovereenkomstig hernoemen.

LuckyTemplates Modulo en Integer-Divide DAX-functies
Power Query Best Practices voor uw gegevensmodel
Power Query M: Afwisselend 0-1-patroon in datumtabel

Conclusie

Je kunt zien dat met alleen de Modulo-truc, en dan de gegevens draaien en met een beetje opschonen, het vrij eenvoudig is om te doen. Als je het eenmaal onder de knie hebt, duurt het waarschijnlijk maar een minuut om het hele ding te doen.

Dus hopelijk heeft dit je wat meer ideeën en tools opgeleverd. Je kunt ook mijn volgende tutorial bekijken . Daarin gaan we dieper in op enkele dingen die we in deze zelfstudie hebben behandeld en kijken we ook naar hoe we dit proces op een herhaalbare manier kunnen automatiseren, met behulp van aangepaste functies. Ik ga je enkele coole trucs laten zien voor het debuggen van je aangepaste functies.

Ik hoop dat je dat nuttig vond. U kunt de volledige video hieronder bekijken en de onderstaande links bekijken voor meer gerelateerde inhoud.

Al het beste!