Kalenderlay-outs naar tabelformaat met behulp van Power Query

In deze zelfstudie leert u hoe u meerdere indelingen van het kalendertype omzet in een tabelindeling met Power Query in LuckyTemplates. U leert hoe u meerdere bestanden kunt transformeren in plaats van een enkel bestand uit een map. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

In deze zelfstudie leert u ook hoe u hiaten in uw gegevens kunt opvullen en hoe u deze kunt verzamelen in LuckyTemplates.

Inhoudsopgave

• De bestanden en lay-outs voorbereiden in de Power Query
• Berekeningen formuleren in de Power Query
• De hiaten in de datums opvullen
• Gescheiden tabellen groeperen
• Conclusie

De bestanden en lay-outs voorbereiden in de Power Query

Dit is de typische lay-out van de dossiers en weken die horizontaal en verticaal zijn uitgezet. Van bestand tot bestand kan het aantal rijen verschillen vanwege de variatie in het aantal rijen.

Ga nu naar Power Query en haal de bestanden op. Selecteer Nieuwe bron en klik op Meer. Klik vervolgens op Alles en kies Map.

Selecteer Parameter en druk op OK.

Het zal je de bestanden in de map laten zien. U moet alle bestanden samenbrengen in één resultaatquery. Als u op Combineren en transformeren klikt, worden de gegevens geladen en worden een aantal query's gemaakt.

Kies vervolgens een willekeurig bestand in de map als een voorbeeldbestand en selecteer een item dat u wilt starten.

Als u naar Query1 kijkt, ziet u gegevens uit zowel Bestand1 als Bestand2.

Berekeningen formuleren in de Power Query

Het formaat van de gegevens in Query1 is nog steeds onjuist. U kunt dit wijzigen via de query Voorbeeldbestand transformeren.

Als u de indeling van die query wijzigt in een tabelvorm, dient deze als een blauwdruk voor alle bestanden in de map.

Verwijder de eerste 2 rijen en Column1.

Vul vervolgens de gaten in de tabel. Ga naar het tabblad Transformeren en selecteer Transponeren. Selecteer de kolommen met datums en klik op de kop. Klik op Vullen en kies Omlaag.

De kolommen met datums in de formulebalk betekenen dat als er een wisselend aantal rijen in elke week is, de resultaten niet in die kolommen staan. Verwijder dus de stap Filled Down in het gedeelte Toegepaste stappen.

Elke groep tabellen begint met een kolom met datums. U moet basisbewerkingen uitvoeren om ze weer in tabelvorm te krijgen.

Voeg een handmatige stap toe door op f(x) in de formulebalk te drukken. Voer vervolgens Table.ToColumns in . Dit verandert elke kolom in een lijst met lijsten.

Verander de lijst in een tabel en voeg een indexnummer toe. U kunt wijzigen van 0 of 1.

De hiaten in de datums opvullen

Er zijn nog hiaten in de data die u moet invullen. De invulbewerking werkt echter niet voor lijsten. U moet deze lijst omzetten in een tabel.

Selecteer Indexkolom en klik op Groeperen op. Vervang Count door ToTable om de kolomnaam te wijzigen. Wijzig RowCount in FromColumns en wijs naar Column1 waar de lijsten zijn geplaatst. Verander het dan in een typetabel.

U kunt nu zien dat de lijst is omgezet in een tabel met slechts één kolom.

Voer nu de opvulbewerking uit voor elke tabel. Voeg eerst een aangepaste kolom toe en noem deze FillDown .

U moet toegang hebben tot de eerste waarde van de eerste kolom in de tabel, dus gebruik IF voor de formule . Verwijs naar de kolom ToTable die de tabel bevat. Voer Kolom1 in als de kolom waarnaar u wilt verwijzen. Gebruik vervolgens null ertussen om de eerste waarde uit de lijst op te halen.

Controleer dan of dat een datum is. Als dat zo is, vult u die kolom in met Table.Filldown. Voer binnen die functie de ToTable en Column1 in. Maar als het geen datum is, voert u ToTable in om de volledige tabel te retourneren.

Dit geeft je een nieuwe kolom met gevulde tabellen.

Zoek waar de kolomdatums worden geplaatst en splits ze op in afzonderlijke tabellen. Om dat te doen, moet u de tabellen omzetten in een lijst met kolommen.

Verwijs na de functies Table.FillDown en Else in de formulebalk naar Column1.

Dit verandert uw tabellen in lijsten met een reeks datums.

Verander de lijst vervolgens weer in een tabel door uw indexkolom bij te werken. Onthoud dat de scheidingsplaats is waar de positie van de datumkolom is. Voeg dus een aangepaste kolom toe en noem deze Index2 . Controleer vervolgens of de lijst datums bevat.

Gebruik IF en voer FillDown in. U hoeft alleen de eerste waarde op de lijst aan te vinken. Als het een datum is, retourneert u Index. Zo niet, voer dan null in . Vul dan die kolom in.

Gescheiden tabellen groeperen

Gebruik Groeperen op in de kolom Index2. Dit groepeert de tabellen met dezelfde Index2-waarden tot één.

Je gaat de rijen niet tellen. Wijzig dus de RowCount in FromColumns en voeg de kolom FillDown toe. Wijzig vervolgens het type in tabel.

Als u op een tabel klikt, ziet u de kolommen erin.

Vervolgens moet u alle afzonderlijke tabellen samenvoegen tot één tabel. Maak een handmatige stap en gebruik Table.Combine. Verwijs vervolgens naar de kolom Aantal erin.

Selecteer Kolom1 en Kolom2. Klik op Transformeren en selecteer Draai andere kolommen ongedaan. Je krijgt dan 4 kolommen te zien.

Verwijder de kenmerkkolom omdat deze niet nodig is. Wijzig vervolgens de namen van de kolommen in Datum , Bewegingen en Naam . Wijzig het gegevenstype van de kolom Datum in Datum en de kolom Naam in Tekst.

U hebt nu de transformaties bijgewerkt naar het transformatievoorbeeldbestand.

Als u naar Query1 gaat, kunt u zien dat Kolom1 van de tabel niet kan worden gevonden. Verwijder dus de stap Gewijzigd type in het gedeelte Toegepaste stappen.

Wijzig ten slotte de gegevenstypen van de kolommen. Stel het gegevenstype van de kolom Datum in op Datum. Stel vervolgens het gegevenstype van de kolommen Bewegingen en Naam in op Tekst.

U hebt nu de indeling van het gegevensmodel bijgewerkt en gewijzigd in een tabelvorm.

Aangepaste LuckyTemplates-kalenders: maand-op-maandwijzigingen berekenen - 445 kalenders
Aangepaste voorwaardelijke opmaaktechnieken in LuckyTemplates
Werken met meerdere datums in LuckyTemplates

Conclusie

In uw gegevensrapport moet u de hiaten in uw datumtabel opvullen door kalenderlay-outs om te zetten in een tabelvorm.

U kunt meerdere kolommen transformeren met behulp van Power Query in LuckyTemplates. Dit geeft uw rapport correcte en waardevolle inzichten om te oogsten.

Al het beste,

Melissa