Gegevens in rijen splitsen met Power Query

In dit bericht laten we u zien hoe u gegevens in rijen kunt splitsen met behulp van Power Query . Tijdens het exporteren van gegevens uit andere systemen of bronnen kunt u situaties tegenkomen waarin de gegevens worden opgeslagen in een indeling waarin meerdere waarden in één cel worden gecombineerd.

Dit kan gebeuren wanneer gebruikers per ongeluk meerdere stukjes informatie in één cel invoeren als ze niet bekend zijn met de conventies van Excel, of wanneer ze gegevens uit een andere bron kopiëren/plakken zonder de juiste opmaak. Het omgaan met dergelijke gegevens vereist extra stappen om de informatie te extraheren en te organiseren voor analyse- of rapportagedoeleinden.

Hoe splits je gegevens in Power Query?

Power Query-transformaties kunnen helpen bij het splitsen van gegevens in afzonderlijke cellen of rijen, op basis van verschillende factoren, zoals scheidingstekens, tekstpatronen en gegevenstypen. U kunt gegevens splitsen door de kolom te selecteren met de gegevens die u wilt splitsen en vervolgens de optie ‘Kolom splitsen’ te gebruiken op het tabblad ‘Transformeren’ in de Power Query Editor. In dit bericht zullen we zien hoe u gegevens in rijen kunt splitsen met behulp van Power Query.

Gegevens in rijen splitsen met Power Query

Om gegevens in rijen te splitsen met Power Query , moet u deze stappen volgen:

Laten we aannemen dat onze gegevens bestaan ​​uit een tekstreeks met scheidingstekens (namen en e-mailadressen gescheiden door een puntkomma), zoals weergegeven in de onderstaande schermafbeelding.

Klik met de rechtermuisknop op de cel die uw gegevens bevat en selecteer Gegevens ophalen uit tabel/bereik in het contextmenu.

Klik op OK in de pop-up Tabel maken en zorg ervoor dat de optie ‘Mijn tabel heeft kopteksten’ is uitgeschakeld. De gegevens worden geopend in de Power Query Editor.

Gegevens opsplitsen in rijen per scheidingslijn

Standaard behandelt Power Query de volledige celinhoud als één waarde en laadt deze als één record met één kolom en één rij. Om de gegevens te splitsen, gaat u naar het menu Kolom splitsen op het tabblad Start en selecteert u Op scheidingsteken . Met deze optie kunt u waarden in de geselecteerde kolom splitsen op basis van een opgegeven scheidingsteken. Met de overige opties in het menu kunt u kolomwaarden splitsen op basis van andere factoren, zoals een bepaald aantal tekens, posities of overgangen.

Zodra u op ‘Door scheidingsteken’ klikt, verschijnt een venster Kolom splitsen op scheidingsteken . Selecteer een scheidingsteken (een teken of reeks tekens die afzonderlijke waarden binnen uw gegevensset scheiden en onderscheiden) in de vervolgkeuzelijst Scheidingsteken selecteren of invoeren . Omdat in ons geval een puntkomma de gegevenswaarden scheidt, hebben we Puntkomma geselecteerd in het vervolgkeuzemenu, zoals weergegeven in de onderstaande schermafbeelding:

Klik vervolgens op de Geavanceerde opties , vouw deze uit en selecteer Splitsen in ‘ Rijen ‘. Klik op OK om de wijzigingen toe te passen.

Nu parseert Power Query uw gegevens, splitst deze bij elke keer dat de puntkomma voorkomt en plaatst elke invoer in een eigen rij.

Zoals je in de bovenstaande schermafbeelding kunt zien, zijn onze gegevens mooi opgesplitst in meerdere rijen. Nogmaals, we zullen de gegevens splitsen om de namen van de e-mailadressen te scheiden.

Gegevens opsplitsen in kolommen per scheidingslijn

Klik op de vervolgkeuzelijst Kolom splitsen onder het gedeelte Transformeren op het tabblad Start en selecteer Op scheidingsteken . Het venster Kolom splitsen op scheidingsteken verschijnt.

Om ervoor te zorgen dat Power Query de gegevens correct kan transformeren, moet u het scheidingsteken correct identificeren en opgeven. In ons geval scheidt een kleiner dan-teken (<) bijvoorbeeld de namen en de e-mailadressen. We selecteren dus Aangepast onder de vervolgkeuzelijst Selecteer of voer het scheidingsteken in en typen het kleiner dan-symbool (in de meeste gevallen zal Power Query automatisch proberen uit te vinden of uw gegevens waarden bevatten, gescheiden door een scheidingsteken, en zo ja, wat dat scheidingsteken is. ).

Deze keer klikken we niet op de vervolgkeuzelijst ‘Geavanceerde opties’, omdat we de standaardwaarde geselecteerd willen laten, namelijk Opgesplitst in ‘Kolommen’. Klik op OK om de wijzigingen toe te passen.

Power Query transformeert uw gegevens en plaatst de namen en e-mailadressen in afzonderlijke kolommen, zoals weergegeven in de onderstaande schermafbeelding:

• U kunt aanvullende stappen uitvoeren om spaties of extra tekens uit uw gegevens te verwijderen. In ons geval staat er bijvoorbeeld een groter dan-teken (>) achter elk e-mailadres. Om er vanaf te komen, klikken we met de rechtermuisknop op de kolomkop en selecteren Waarden vervangen . Vervang vervolgens ‘<‘ door niets (voer ‘<‘ in het veld ‘Te vinden waarde’ in en laat het veld ‘Vervangen door’ leeg).
• U kunt elke kolom een ​​beschrijvende naam geven door op de kop te dubbelklikken.

Als u klaar bent, klikt u op de knop Sluiten en laden in de linkerbovenhoek van de Power Query Editor om de gegevens naar een nieuw Excel-werkblad te exporteren.

Dat is het! Ik hoop dat je dit nuttig vindt.

Hoe zet u kolommen om in rijen in Power Query?

Selecteer de kolommen waarvan u rijen wilt maken. Ga naar het tabblad Transformeren . Ga vervolgens naar het menu Unpivot Columns binnen de groep ‘Elke kolom’ en selecteer Unpivot Only Selected Columns . Power Query transformeert de geselecteerde kolommen in rijen en maakt twee nieuwe kolommen, één voor attribuutnamen en één voor de bijbehorende waarden. Hernoem de kolommen indien nodig en klik op de knop Sluiten en laden om de wijzigingen in uw gegevens op te slaan.