Eeuwigdurende vakantiekalender - POTW #12 (Power Query-oplossing)

In deze blogpost onderzoeken we mijn oplossing voor . Voor deze uitdaging hebben we de eerste Probleem van de week-uitdaging van LuckyTemplates opnieuw bekeken, waar we een eeuwigdurende vakantiekalender hebben gemaakt op basis van een mix van specifieke en relatieve datums. Maar deze keer gebruiken we alleen Power Query . Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Als je vastzit aan de probleemanalyse zelf, kun je de opnieuw bekijken om te zien of er elementen zijn in een van de benaderingen die je kunnen helpen bij deze uitdaging.

Tijdens het lezen van de inzendingen heb ik een mix van mensen gezien die gebruik maakten van de gebruikersinterface en aangepaste functies schreven om een ​​eeuwigdurende kalender te maken. Het was leuk om elke inzending te analyseren, en als je geïnspireerd wilt raken, moedig ik je aan hetzelfde te doen.

Toen ik dit scenario zelf doorwerkte, bedacht ik uiteindelijk meerdere oplossingen. Degene die ik heb gevolgd, illustreert het beste hoe je een probleem niet alleen in kleinere stukjes kunt opsplitsen , maar ze ook zichtbaar kunt maken . Dit is erg handig als u een fout of onverwachte resultaten tegenkomt.

Inhoudsopgave

• Het startpunt voor het maken van een eeuwigdurende kalender
• De jaren ophalen uit de datumtabel
• Het cijfer uit de dagkolom halen
• Een beroep doen op de aangepaste functie voor de eeuwigdurende vakantiekalender
• Een lijst met datums maken
• Voorwaardelijke logica toepassen
• Herziening van de M-code voor de eeuwigdurende vakantiekalender
• Conclusie

Het startpunt voor het maken van een eeuwigdurende kalender

Deze rauwe feesttafel is ons uitgangspunt. Om een ​​datum samen te stellen, hebben we een jaar, een maand en een dag nodig voor specifieke datums. Het enige wat ontbreekt is het jaartal.

Maar voor relatieve datums moeten we wat meer werk verzetten. Om te beginnen moeten we die dagkolom splitsen in zowel het cijfer als de weekdag. Laten we die stappen eens bekijken.

De jaren ophalen uit de datumtabel

Om deze oplossing dynamisch te maken, ga ik de jaren uit de datumtabel halen . In de formulebalk kun je zien dat ik een projectie heb gebruikt om een ​​tabel met één kolom uit mijn datumtabel te halen. Dit zal natuurlijk duplicaten bevatten. Om die te verwijderen, heb ik Table.Distinct toegevoegd .

Als ik opzij klik in de witte ruimte naast een van deze tabellen, zie je ook een lijst met de verschillende jaren die aanwezig zijn in mijn datumtabelquery.

We hebben een maandnaam gekregen , maar we hebben eigenlijk een maandnummer nodig . Als we nu een jaar aan een maandnaam koppelen, kunnen we de functie Date.From gebruiken om de eerste van die maand als datumwaarde te retourneren.

Door Date.Month toe te voegen , houden we alleen het maandnummer voor die specifieke datum over. Voor januari wordt een 1 geretourneerd. Voor februari wordt een 2 geretourneerd, enzovoort.

Het cijfer uit de dagkolom halen

Om het cijfer uit de dagkolom te halen, hebben we besloten de kolom niet te splitsen en in plaats daarvan de waarde te extraheren. Zoals je kunt zien in de formulebalk, hebben we Text.BeforeDelimiter gebruikt en die spatie gebruikt om het rechter- en linkergedeelte van die tekenreeks te identificeren.

Als er geen ruimte is, willen we teruggeven wat er nog over is in de dagkolom. Dus voor de eerste record retourneert dat een 1. Voor de tweede record die die spatie bevat, retourneert het het eerste deel van de tekenreeks, het woord Derde .

We zullen een vergelijkbare benadering gebruiken om de datumnaam te krijgen, maar gebruiken de functie List.PositionOf . Dit retourneert de op nul gebaseerde offset van de waarde in de lijst die als eerste argument is doorgegeven, en retourneert -1 als de waarde niet in die lijst voorkomt.

Voor het eerste record wordt de waarde 1 doorgegeven, die niet aanwezig is in onze lijst. Daarom retourneert het de waarde -1. Voor het tweede record wordt de waarde Monday geretourneerd .

Het geeft dat door aan onze functie en retourneert een 1 omdat dit het tweede item in onze lijst is. Vervolgens vervangen we alle -1 waarden door een null .

Op dit punt hebben we alle bouwstenen gemaakt die we nodig hebben om de aangepaste functie aan te roepen.

Een beroep doen op de aangepaste functie voor de eeuwigdurende vakantiekalender

In de formulebalk kunnen we zien dat de functie vier argumenten nodig heeft: een jaar , een maand , een cijfer en een weekdag .

Maar hoe bepaalt het die datumwaarde? We zullen de List.PositionOf opnieuw gebruiken om een ​​numerieke waarde voor het cijfer te krijgen. We hebben een lijst met eerste, tweede, derde en vierde. Wanneer we het cijfer doorgeven aan de List.PositionOf , retourneert het de op nul gebaseerde offset.

Nogmaals, als een waarde niet aanwezig is, wordt een -1 geretourneerd. Dus voor de eerste record geven we de waarde 1 door. 1 is niet aanwezig in deze lijst, daarom retourneert het de waarde -1. Voor het tweede record geven we de waarde Third door, het derde item in onze lijst. Dit geeft de waarde van 2 terug.

Een lijst met datums maken

Laten we nu een lijst met datums maken. We bepalen de startdatum voor die maand, bepalen het aantal dagen in die maand en gebruiken List.Dates om een ​​lijst te maken met alle dagen in die maand.

Vervolgens gebruiken we List.Select om alleen die datums te behouden die op een specifieke weekdag vallen.

Wanneer we naast een van deze lijsten opzij klikken, kunnen we zien dat deze alleen deze vier items bevat.

Voorwaardelijke logica toepassen

Ten slotte passen we voorwaardelijke logica toe om een ​​datum te construeren of op te halen. Als de weekdag null is , wat aangeeft dat we een specifieke datum hebben, kunnen we de intrinsieke #date gebruiken om een ​​datumwaarde te construeren.

Als het cijfer gelijk is aan het laatste, kunnen we de functie List.Reverse gebruiken om de volgorde in de lijst om te keren en de eerste waarde te extraheren.

Als we deze oplopende volgorde willen wijzigen in aflopende volgorde, kunnen we altijd de laatste datum uit dat datumbereik halen. Het maakt niet uit of we vier of vijf elementen in de lijst hebben. Door de volgorde om te draaien en het eerste item eruit te halen, hebben we altijd de laatste datum.

Voor alle andere gevallen gebruiken we List.Skip .

We kunnen de waarde extraheren op basis van deze positie op de lijst. Voor Thanksgiving is het cijfer de vierde en de vierde geeft een 3 terug. Hierdoor worden de eerste drie waarden in de lijst overgeslagen en wordt altijd het vierde item geretourneerd.

Laten we alles samenvoegen en naar de oplossingsvraag gaan. Voor de bronstap noemen we de RAW-vakantiedatumtabel. Vervolgens voegen we een kolom toe die een lijst met datums retourneert.

Herziening van de M-code voor de eeuwigdurende vakantiekalender

Laten we de M-code voor de kolom controleren. Voor elke rij in de tabel hebben we een tabel genest met de verschillende jaren uit de datumtabel. Vervolgens hebben we de waarden in die jaarkolom getransformeerd door de aangepaste functie aan te roepen.

Om de aangepaste functie te kunnen gebruiken, moeten we natuurlijk eerst wat instellen. We moeten het maandnummer , de cijferwaarde en natuurlijk de weekdag krijgen . Vervolgens hebben we vanuit die geneste tabel de lijst met datums bijgehouden.

Als we opzij klikken in de witte ruimte, zien we hieronder een voorbeeld van die lijst.

De volgende stap is om die lijst uit te breiden en de CelebratedOnDate toe te voegen .

Dit is de logica die we in deze berekening hebben gebruikt: als de datum op een zaterdag valt, dan gaan we een dag aftrekken zodat we op een vrijdag uitkomen. Als het een zondag is, voegen we een dag toe om op een maandag te landen. Als het geen zaterdag of zondag is, dan willen we een nul.

Ten slotte is de laatste stap het wijzigen van de typen.

Conclusie

Even een herinnering dat een doorlopende serie is die elke 1e en 3e woensdag plaatsvindt op het LuckyTemplates-forum. Het probleem dat in de eerste week is gepost, vereist een op DAX gebaseerde oplossing, terwijl het probleem in de derde week om een ​​op Power Query gebaseerde oplossing vraagt.

Als je de inhoud van deze POTW leuk vond, abonneer je dan op het LuckyTemplates TV-kanaal voor meer. Er komt voortdurend een enorme hoeveelheid content uit van mijzelf en een reeks contentmakers, allemaal toegewijd aan het verbeteren van de manier waarop u LuckyTemplates en het Power Platform gebruikt.

Melissa