Lay-out van kalendertype omzetten in tabelindeling in LuckyTemplates met Query-editor

Het analyseren van de gegevens die we hebben, kan moeilijk zijn als ze niet correct zijn gerangschikt. In deze zelfstudie leg ik uit hoe u verticale en horizontale herhaalde gegevens omzet in een tabelindeling. We doen dit zodat we het gemakkelijker kunnen analyseren met LuckyTemplates. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Deze tutorial is gemaakt om de vraag van een  lid van te beantwoorden  . Het lid vroeg om een ​​manier waarop hij zijn gegevens van een kalenderachtige lay-out naar een tabelindeling kon omvormen.

Laten we, voordat we beginnen, eerst de gegevens bekijken die we hebben.

Hier hebben we een gegevenslay-out waarin de dagen horizontaal zijn ingesteld en de weken verticaal. Dit type opstelling is bij de meesten van ons bekend, omdat dit de manier is waarop we de gegevens gemakkelijk kunnen begrijpen.

Dit type lay-out is echter moeilijker te analyseren omdat er veel kolommen en rijen zijn waarmee rekening moet worden gehouden.

Met dat gezegd zijnde, laten we de gegevens transformeren naar een goed tabelformaat waar we eindigen met een kolom voor datums, een kolom voor de bewegingen en een andere kolom voor de namen.

Inhoudsopgave

• Gegevens opschonen voor een juist tabelformaat
• De tafels breken
• Identificatie van de lijst voor de nieuwe tabellen voor een juist tabelformaat
• Bepalen welke waarden in de tabel thuishoren
• De gegevens groeperen voor een juist tabelformaat
• De afzonderlijke tabellen toevoegen
• Conclusie

Gegevens opschonen voor een juist tabelformaat

Laten we, om het u gemakkelijker te maken, onze gegevens in de Power Query-editor transformeren . Maar voordat we dat doen, zorgen we ervoor dat de formulebalk zichtbaar is op uw scherm. Als dit niet het geval is, gaat u naar het tabblad Weergave en vinkt u het vakje naast de formulebalk aan.

De eerste stap is het verwijderen van de onnodige waarden in onze data. Laten we de eerste kolom en de bovenste twee rijen van onze gegevens verwijderen, omdat we ze niet echt nodig hebben. Klik hiervoor op het tabblad Rijen verwijderen, klik op Bovenste rijen verwijderen, voer 2 in en druk op OK.

En om nu de eerste kolom te verwijderen, gaat u naar het tabblad Kolommen kiezen en deselecteert u kolom 1 door het vakje ernaast uit te vinken.

Laten we ook de rijen index 14 tot en met 16 verwijderen, aangezien deze volledig zijn gevuld met alleen null-waarden. Ga opnieuw naar het tabblad Rijen verwijderen en klik vervolgens op Lege rijen verwijderen .

Een ander probleem dat we hebben, is dat de datumwaarden elke keer een kolom overslaan. De datums worden altijd gevolgd door een null-waarde, zoals hieronder aangegeven. Dit gebeurt niet alleen op de bovenste rij, maar ook op de rijen eronder.

Om deze hiaten op te vullen, gaan we eerst onze gegevenstabel transponeren door naar het  tabblad Transformeren te gaan en vervolgens op de knop Transponeren te klikken.

De tafel zou er nu uit moeten zien alsof hij op zijn kant is gekanteld na het uitvoeren van de actie.

Selecteer vervolgens alle kolommen die de datums bevatten, klik met de rechtermuisknop op de kolomkop en selecteer vervolgens Opvullen.

Dit zou de datums moeten dupliceren en de hiaten in de geselecteerde kolommen moeten opvullen.

De tafels breken

Kijkend naar de data die we nu hebben, zien we dat er vier tabellen naast elkaar staan. Kolommen 1 tot en met 7 vormen één tabel, kolommen 8 tot en met 13 vormen een andere tabel, enzovoort.

Om deze in één enkele tabel te voegen, moeten we een eenvoudige unpivot- bewerking uitvoeren om deze om te zetten in een correct tabelformaat. Maar aangezien de tafels naast elkaar staan, kunnen we dat niet zomaar doen. We moeten deze tafel uit elkaar halen en ze weer aan elkaar naaien tot kleinere tafels.

Om dat te doen, maken we de eerste formule voor deze zelfstudie. Laten we op de fx- knop klikken en vervolgens de formule toevoegen die we gaan gebruiken. We gaan de bewerking Table.ToColumns gebruiken . Hierdoor wordt een lijst met geneste lijsten met kolomwaarden uit een tabel gemaakt. Elke kolom wordt omgezet in een lijst.

Dit is hoe ik de formule heb opgezet.

De formule maakt een lijst met de kolommen die we eerder hadden. De eerste waarde van de lijst bevat kolom 1, de tweede waarde bevat kolom 2, enzovoort.

Identificatie van de lijst voor de nieuwe tabellen voor een juist tabelformaat

Nu moeten we vaststellen welke waarden op de lijst datumwaarden bevatten. Zodra we weten waar deze data zich bevinden, kunnen we bepalen waar elk van onze afzonderlijke tafels begint of eindigt.

Om dat te doen, zetten we eerst de lijst weer om in een tabel door op de knop Naar tabel in de linkerbovenhoek te klikken.

Voeg er vervolgens een indexkolom naast toe.

Het volgende dat we gaan doen is een andere kolom toevoegen naast de indexkolom. Deze kolom zou ons moeten helpen bepalen welke waarden op Kolom1 de datumwaarden bevatten. Laten we deze kolom Index2 noemen.

Om dat te doen, gaan we een formule opnemen in Index2, die de indexwaarde van Kolom1 retourneert als deze een datum bevat.

We gaan de gebruiken en verwijzen vervolgens naar Kolom1. De haakjes zullen dienen als operator; het toevoegen van 0 tussen deze haakjes betekent dat we de eerste waarde uit de lijst krijgen; dan bepalen we of het een date is. Als dat zo is, wil ik dat de formule het indexnummer retourneert; als dat niet het geval is, wil ik helemaal niet dat het enige waarde retourneert.

Aangezien de eerste waarde in Kolom1 een datum bevat, moet de eerste waarde in Index2 het indexnummer 0 bevatten. Aangezien de tweede waarde in Kolom1 geen datum bevat, mag de tweede waarde in Index2 alleen een null-waarde bevatten.

Dit is hoe ik de formule voor Index2 heb opgesteld. Zorg ervoor dat er geen syntaxisfouten in uw formule voorkomen.

Daarna zou onze Index2-kolom nu de index moeten hebben van de waarden in Kolom1 die de datum bevatten. Het bevat ook enkele null-waarden als resultaat van de formule die we hebben ingesteld.

Bepalen welke waarden in de tabel thuishoren

Nu geven deze indexwaarden binnen Index2 het begin en einde aan van de afzonderlijke tabellen die we hebben. Dat wil zeggen, 0 markeert het begin van de eerste tafel, 7 markeert het einde van de eerste tafel en het begin van de tweede tafel, enzovoort.

Om ons te helpen identificeren welke waarden in Kolom1 in dezelfde tabel thuishoren, passen we een opvulactie toe in onze nieuwe kolom.

Na het uitvoeren van de actie moeten de null-waarden nu worden vervangen door nullen, zevens, dertien en twintig. Deze nummers geven aan dat ze in dezelfde tabel thuishoren. Dat wil zeggen, de nullen vormen één tabel, de zevens vormen een andere tabel, enzovoort.

De gegevens groeperen voor een juist tabelformaat

Aangezien we al weten welke waarden in Kolom1 in dezelfde tabel thuishoren, gaan we ze nu groeperen. Om dat te doen, gebruiken we de bewerking Groeperen op.

Door dat te doen, eindigen we nu met slechts 4 waarden in onze Index2-kolom. Merk op dat een andere kolom met de naam Count naast onze Index2-kolom staat. Deze kolom telt het aantal rijen in elk van onze waarden in de Index2-kolom.

Dit is echter niet echt wat we willen doen. Wat we moeten doen, is een nieuwe tabel vormen met behulp van de kolommen die we hebben.

Om dat te doen, gaan we de bewerking Table.FromColumns gebruiken , verwijzen naar de kolom die onze lijstwaarden bevat, namelijk Column1, en vervolgens het retourtype wijzigen in een tabel.

Het is belangrijk om ervoor te zorgen dat we naar de juiste kolom in onze formule hebben verwezen. Controleer of Kolom1 echt de lijstwaarden bevat.

Hiermee worden alle waarden in Kolom1 met een Index2-waarde van 0 samengevoegd in één tabel, die met de Index2-waarde van 7 in een andere tabel, enzovoort.

We hebben elk record gecontroleerd op waarde 0 en vervolgens de lijstwaarde opgehaald uit Kolom1. Uiteindelijk hebben we die lijsten samengevoegd tot één tabel met behulp van deze M-code.

We pakten de eerste 7 kolommen en hechtten ze aan een enkele tabel, pakten de volgende paar kolommen, hechtten ze aan een andere tabel, enzovoort.

De afzonderlijke tabellen toevoegen

Nu we ze al hebben gegroepeerd, gaan we proberen alle tabellen toe te voegen door nog een stap toe te voegen. Nogmaals, laten we op de fx- knop drukken om een ​​nieuwe formule toe te voegen.

De bewerking Table.Combine voegt een lijst met tabellen samen en maakt er vervolgens één enkele tabel van. Hier gaan we verwijzen naar de kolom die onze lijst met tabellen bevat, de kolom Aantal.

We hebben nu een tabel waarin alle geneste tabellen weer zijn toegevoegd tot één grote enkele tabel.

Nadat we alle tabellen hebben toegevoegd, kunnen we nu de laatste paar stappen uitvoeren om onze gegevens om te zetten in een correct tabelformaat. Selecteer de eerste twee kolommen van uw tabel en klik vervolgens op de knop Andere kolommen ongedaan maken op het tabblad Transformeren.

Verwijder daarna de attribuutkolom omdat we deze niet echt nodig hebben. Ga naar Kolommen kiezen en deselecteer vervolgens de kenmerkkolom.

Stel ten slotte de juiste typen van alle kolommen in en label ze op de juiste manier.

Dubbelklik op de kolomkop van elke kolom om hun naam dienovereenkomstig te wijzigen.

Wijzig voor de eerste kolom het type in datum, aangezien deze onze datumwaarden bevat.

Selecteer vervolgens de tweede en derde kolom en stel hun typen in op tekst.

Helemaal klaar! Dit is hoe ons juiste tabelformaat eruit zou moeten zien.

Conclusie

In deze zelfstudie hebben we enkele gegevens, die in een lay-out van het kalendertype werden gepresenteerd, getransformeerd naar een geschikt tabelformaat. We hebben eerst onze data opgeschoond, opgesplitst in kleinere tabellen, de tabellen die bij elkaar horen gegroepeerd en uiteindelijk allemaal toegevoegd.

Deze tutorial biedt u veel kennis over het correct modelleren van uw gegevens voor eenvoudigere analyse.

Het begrijpen van de concepten die hier zijn gebruikt, is erg belangrijk, omdat ze ook op veel andere manieren kunnen worden toegepast.

Als de concepten u nog steeds niet duidelijk zijn, kan het controleren van de onderstaande links u misschien helpen.

Veel plezier met het doorwerken van deze!

Melissa